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