Follow these best practices to make your queries faster and efficient

Note: Please research more about each topic before applying

Thread 🧵 


### 🦋 1. Add Index key columns 🔥 🔥 🔥

🔸 Always index columns that you plan on searching on
🔸 Index the columns which are used in JOIN clauses so that the query returns the result fast
🔸 Do not use Index for many column, it will slow the Insert or Update


### 🦋 2. LIMIT 1 When Getting a Unique Row 🔥 🔥 🔥

🔸Adding LIMIT 1 to your query can increase performance
🔸Reduces execution time because the database engine will stop scanning for records after it finds the first match


### 🦋 3. Always use proper datatype 🔥 🔥 🔥

🔸If you use irrelevant datatypes it may consume more space or may lead to errors
🔸MySQL query optimizer will choose the indexes based on data type you used in query


### 🦋 4.  Use SELECT * only if needed 🔥 🔥 🔥

🔸If there are many columns in the table, all will get returned which will slow down the response time
🔸Particularly if you send the result to a front-end application
🔸Do this instead: SELECT (name,email) ... only


### 🦋 5. Normalize your data and use joins 🔥 🔥 🔥

🔸Database normalization is the process of organizing columns and tables in relational database to avoid redundancy
🔸This will reduce the column or table and improve the performance



### 🦋 6. Use CHAR (1) over VARCHAR(1) 🔥 🔥 🔥

🔸If you string a single character, use CHAR(1) instead of VARCHAR(1)
🔸VARCHAR(1) will take extra byte to store information



### 🦋 7. Log queries in development 🔥 🔥 🔥

🔸It is great to see queries happening in real-time
🔸You will get any error raised behind the scene and you can fix them
🔸It can help you a lot while you are developing.


### 🦋 8. Avoid using regional date formats 🔥 🔥 🔥

🔸Use DATETIME or DATE datatype always use YYYY-MM-DD date format or ISO date format 
🔸This will store exact time and later on your code you can change it as you want


### 🦋 9. Always try to avoid redundancy 🔥 🔥 🔥
🔸Best database is considered as the one with low or no redundancy
🔸Normalize your database schema to avoid redundency


### 🦋 10. Always use ID of any column to refer on another table 🔥 🔥 🔥

🔸Connecting tables with IDs like blog_user table connect blog & user table with just 2 fields "user_id" and "blog_id"
🔸Add foreign key on these fields to refer to original table

### 🦋 Liked this post
subscribe to bitfumes newsletter https://bitfumes.com/newsletters
Follow me on twitter [@Sarthaksavvy](https://twitter.com/sarthaksavvy)

10 Best practices in MySQL

General

Sarthak Shrivastava