Problem: a MySQL query containing many joined tables is extremely slow (more than 1 minute). Proper indexes are present and being used according to EXPLAIN. The number of processed and returned rows is small (the problem persists with a COUNT(*) query).

Diagnosis: you may be hitting a performance problem with the MySQL query optimizer, which computes an "optimal" execution plan before the query is executed. It is difficult to find out without instrumentation (see example below on how to measure the time spent in optimizer), but it is easy to try whether the recommended solution helps.

Solution: change the MySQL parameter optimizer_search_depth, e.g. SET optimizer_search_depth=0 in the session before executing the query.

How to measure time spent in the MySQL query optimizer

On platforms which support DTrace and with an appopriate version of MySQL (I tested with 5.1.46, OpenSolaris), you can use the following DTrace script: