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 bitfumes.com/newsletters Follow me on twitter @Sarthaksavvy