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

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.

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;

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.