I recently encountered a situation where I had a query that was built with quite a few joins (~8), and I found that the query was taking a bit longer than I expected, especially for the number of rows to look at / return.  With each join, you add in more complexity for MySQL to handle in how to best utilize indexes, etc.

I found that tweaking the ‘optimizer_search_depth‘ parameter (in my.cnf) for MySQL significantly sped up the query, as it told MySQL to only go so far in finding the best way to execute the query.

You can read more about it here: http://dev.mysql.com/doc/refman/5.0/en/controlling-optimizer.html

I’d recommend tweaking this variable to find the best level for your worst query.  For simple queries, they should be unaffected since MySQL doesn’t need to do much analysis on the best execution plan.  As an example, I’ve set the value to ‘3’, and things are just fine.

To determine if you need this tweak, you can login to the MySQL console while a long-running process is executing and execute ‘SHOW PROCESSLIST\G‘.  If you see a query in ‘statistics’ state for more than a few seconds, try tweaking the value, and re-running the process.

Leave a Reply