MySql Query Optimization Tips
Here you can find some tips to optimize slow queries that have been useful to me.
- Choose the Right Engine
- Optimize the Query
- Create Indexes
- Force the Right Index
- Mind the Join Order
- Consider Partitioning Table
The 3 main mysql storage engines I use for the moment are
- InnoDB is the slowest but mandatory if you need transactions or you don't want to risk data corruption.
- MyISAM is faster but it only locks at a table level, better if you need large amount of inserts and updates.
- Memory is the fastest Engine. It's a good option in you only need to work with the data temporarily. The data will be lost once the server is restarted. Ram memory will be taken up by the table.
- Use EXPLAIN SELECT * FROM table or EXPLAIN EXTENDED SELECT * FROM table to find out more about the query execution plan. ref. http://dev.mysql.com/doc/refman/5.0/en/explain.html and https://dev.mysql.com/doc/refman/5.0/en/explain-output.html
SELECT SQL_NO_CACHE * FROM TABLEor
SELECT *, now() FROM TABLEnot to cache the query while optimizing the query. (Mind that the second query normally will be faster the first one, no matter if you disable the cache, the SO is also involved in the caching procress therefore Mysql is faster because it only has to read from memory)
- Use profiling to know what part is taking more cpu/time to complete, e.g.
SET profiling = 1; SHOW PROFILE; SHOW PROFILE FOR QUERY 1;
SHOW PROFILE CPU FOR QUERY 1;
- Create Indexes to improve performance. When created the EXPLAIN SELECT the number of rows should be reduced as close as 1 as possible.
- A Multiple-Column index is more efficient that separate single indexes. If you query is meant to have always the same amount of WHERE conditions for the same TABLE consider using a mutiple column index. ref. http://dev.mysql.com/doc/refman/5.0/en/optimization-indexes.html
- Consider launching the Optimize Table command after you finish creating indexes.
- Use IGNORE INDEX, FORCE INDEX, USE INDEX to choose different indexes (if needed) than the ones assigned by default. ref. http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
- In some cases the query optimzer puts the most appropiate table in the wrong order. With STRAIGHT_JOIN we can fix that.
- If you query for a specific value of a big table consider partitioning this table. http://dev.mysql.com/doc/refman/5.6/en/partitioning.html, to manage the partition http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-range-list.html
Much more information at http://dev.mysql.com/doc/refman/5.0/en/optimization.html