Solution for MySQL query slow, index does not help

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:

#!/usr/sbin/dtrace -s
/*
 * Measures and shows time spent in the mysql query optimizer.
 *
 * Usage: ./mysql-optimizer-check.d -p `pgrep -x mysqld`
*/
#pragma D option quiet

pid$target:mysqld:*mysql_parse*:entry
{
    printf("Query: %s\n", copyinstr(arg1));
}

pid$target:mysqld:*JOIN*optimiz*:entry
{
    self->start = timestamp;
    printf("Query optimizer entered\n");
}

pid$target:mysqld:*JOIN*optimiz*:return
{
    this->elapsed = timestamp;
    this->nano = this->elapsed - self->start;
    this->time1 = (this->elapsed - self->start)/1000000000;
    this->time2 = ((this->nano % 1000000000) * 100) / 1000000000;
    printf("Time spent in optimizer: %d.%ds\n", this->time1, this->time2);
    this->elapsed = 0;
    self->start = 0;
}

To run this script in OpenSolaris:

pfexec ./mysql-optimizer-time.d -p `pgrep -x mysqld`

Sample output (first query executes instantly, second query hangs and is aborted by myself after 7.72s):

Query: select @@version_comment limit 1
Query optimizer entered
Time spent in optimizer: 0.0s
Query: SELECT
    DISTINCT appl_app_id_appl.appl as 'appl.app_id_foreign',
    appl.app_id as 'appl.app_id',
    appl_app_id_appl.app_id as 'appl_app_id_appl.app_id',
    appl_app_id_appl.mandant_id as 'appl_app_id_appl.mandant_id',
    appl_app_id_appl.appl as
Query optimizer entered
Query: KILL /*!50000 QUERY */ 9
Time spent in optimizer: 7.72s
^C