Tips to optimize MySQL Database

Sep 07, 2013, by admin

Hi all hope you all are enjoying the weekend today in this post we are going to see some tips to optimize MySQL Database the steps are given below

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.

To get more updates like the page Bugtreat Technologies