Article updated on

MySql Query Optimization Tips

Here you can find some tips to optimize slow queries that have been useful to me.

  1. Choose the Right Engine
  2. Optimize the Query
  3. Create Indexes
  4. Force the Right Index
  5. Mind the Join Order
  6. Consider Partitioning Table

1 - Choose the Right Engine

The 3 main mysql storage engines I use for the moment are  InnoDB, MyISAM, and Memory.

  • 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.

2 - Optimize the Query

  • 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
  • Use SELECT SQL_NO_CACHE * FROM TABLE or SELECT *, now() FROM TABLE not 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;

3 - Create Indexes

  • 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.

4 - Use the Right Index

5 - Mind the Join Order

  • In some cases the query optimzer puts the most appropiate table in the wrong order. With STRAIGHT_JOIN we can fix that.

6 - Consider Partitioning Table

Note

Much more information at http://dev.mysql.com/doc/refman/5.0/en/optimization.html