TIPS to Optimize Your MySQL Database
These are the Database Optimization guidelines that I follow when writing my code, as a coding standard is already exists for the project I’ m working on. These can be helpful for the developer as it assist developers to know do’s and don’ts during the Database Optimization and coding practices. Actually Database Optimization TIPS helps any system to perform work efficiently and quickly.
1. Always use the Transactions in long running queries
Always use the Transactions in long running queries if there is expected a long running transactions and expecting large records output. You can use the transactions within the BEGIN TRAN and END TRAN. Using transactions this way, it will holds itself a place in buffer cache for individual transactions and also memory is assigned for making it faster.
2. DON’T Use SELECT *
One of the most common performance and scalability problems are queries that return too many columns or too many rows. I’ve seen many developers actually using and abusing SELECT * FROM queries. SELECT * query not only returns unnecessary data, but it also can force clustered index scans for query plans because columns in the SELECT clause are also considered by the optimizer when it identifies indexes for execution plans.
3. Avoid explicit or implicit functions in WHERE clauses
Always try to avoid explicit or implicit functions in WHERE clauses.
4. Do replace all sub queries with joins
Sub queries treat itself as inline code whereas Joins use itself as Table which is faster.
If possible try to avoid the sub queries within join.
5. Use UNION ALL instead of UNION
To improve query speed, use the UNION ALL statement. UNION ALL looks for and discards duplicate rows in the result set, whereas the UNION statement does not.
6. Use stored procedures or parameterized queries.
7. Use temporary tables and table variables appropriately.
8. DON’T Use long actions in triggers.
9. Avoid expensive operators such as NOT LIKE.
10. Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
11. Create composite indexes with the most restrictive column first.
Follow @phpzag
