Tips to optimize MySQL query performance

The most common issue that bothers MySQL users is slow performance of database. There is nothing worst than having a slow database that takes forever to open. To optimize the overall performance of your database, you first need to optimize the queries. Because badly design queries not only degrade the performance of database but also consume more resources. So first try to find out the bad queries that can impact your database and then optimize them.

1. EXPLAIN keyword helps in optimizing the query by displaying information about your query execution plan. To know the details about query, precede your SELECT statement with EXPLAIN keyword. Using this keyword you can see whether the tables are joined properly, how to add indexes in tables to makes the query faster and other information.
2. Add index in your tables to speed up query. Index helps in finding record quickly; you can search for any desired record without scanning the whole table.
3. Instead of using DISTINCT clause, use GROUP BY as it is more convenient.
4. Avoid using HAVING clause where you can use WHERE.
5. Use OPTIMIZE TABLE command to remove the unused space.
6. Use ORDER BY clause with inequalities.
7. Instead of having a very large query, create smaller sub queries.
8. Avoid using ORDER BY RAND() for large records.
9. Optimize following variables of MySQL to improve performance:

• Table Buffer Size(read_buffer_size): The buffer size is set when a query scan table sequentially. You can increase the value of buffer size to improve database performance.
• Index Buffer Size (Key_Buffer): Key_Buffer controls the size of buffer that is used to handle table read and write operation. To improve the performance, set its value to atleast 25 percentage of total system memory.
• Long Queries (long_query_time): MySQL has a slow_query_log to show all the queries that are consuming most of the resources and taking too long to finish. To improve the performance of MySQL you can set the maximum time limit for long_query_time.

10. Use SHOW PROCESS LIST command to see which queries are running and creating problem.
11. You can check how much time a query or expression is taking in execution by using benchmark () function.
12. Avoid using * as it overkill performance.
13. Avoid using wildcard in the beginning of LIKE pattern.