'MYSQL Profiling fast - query slow?

I got a MYSQL query which profiles very fast (0.0004 Sek) but when executed it takes more than 30 seconds. Its a standalone dedicated server, so some power behind it.

You see the query down below. I'm sure it can be drastically improved. I just don't understand where the difference between the profiling values and the actual execution time comes from.

SELECT DISTINCT x.loc_id AS id_a, 
hier2.loc_id AS id_b, hier3.loc_id AS id_c, hier5.loc_id AS id_d, hier6.loc_id AS id_e, hier7.loc_id AS id_f, hier8.loc_id AS id_g, 
hier2.loc_name AS name_b, hier3.loc_name AS name_c, hier5.loc_name AS name_d, hier6.loc_name AS name_e, hier7.loc_name AS name_f, hier8.loc_name AS name_g 

FROM objects AS o 

LEFT JOIN geo_xref AS x ON o.id = x.oid 
LEFT JOIN locations AS hier2 ON x.hier2 = hier2.loc_id 
LEFT JOIN locations AS hier3 ON x.hier3 = hier3.loc_id 
LEFT JOIN locations AS hier5 ON x.hier5 = hier5.loc_id 
LEFT JOIN locations AS hier6 ON x.hier6 = hier6.loc_id 
LEFT JOIN locations AS hier7 ON x.hier7 = hier7.loc_id 
LEFT JOIN locations AS hier8 ON x.hier8 = hier8.loc_id 

WHERE o.published = 1 AND x.loc_id IS NOT NULL

EXPLAIN

<table id="table_results" class="data">
<thead><tr>
<th>id
</th><th>select_type
</th><th>table
</th><th>type
</th><th>possible_keys
</th><th>key
</th><th>key_len
</th><th>ref
</th><th>rows
</th><th>Extra
</th></tr>
</thead>
        <tbody>
    <tr class="odd">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">x</td>
    <td class="">ALL</td>
    <td class="">PRIMARY,loc_id,xcon</td>
    <td class=""><i>NULL</i></td>
    <td class=""><i>NULL</i></td>
    <td class=""><i>NULL</i></td>
    <td align="right" class=" nowrap">187</td>
    <td class="">Using where; Using temporary</td>
</tr>
            
    <tr class="even">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">o</td>
    <td class="">eq_ref</td>
    <td class="">PRIMARY</td>
    <td class="">PRIMARY</td>
    <td class="">4</td>
    <td class="">db.x.oid</td>
    <td align="right" class=" nowrap">1</td>
    <td class="">Using where</td>
</tr>
            
    <tr class="odd">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">hier2</td>
    <td class="">ref</td>
    <td class="">loc_id</td>
    <td class="">loc_id</td>
    <td class="">4</td>
    <td class="">db.x.hier2</td>
    <td align="right" class=" nowrap">5</td>
    <td class=""><i>NULL</i></td>
</tr>
            
    <tr class="even">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">hier3</td>
    <td class="">ref</td>
    <td class="">loc_id</td>
    <td class="">loc_id</td>
    <td class="">4</td>
    <td class="">db.x.hier3</td>
    <td align="right" class=" nowrap">5</td>
    <td class=""><i>NULL</i></td>
</tr>
            
    <tr class="odd">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">hier5</td>
    <td class="">ref</td>
    <td class="">loc_id</td>
    <td class="">loc_id</td>
    <td class="">4</td>
    <td class="">db.x.hier5</td>
    <td align="right" class=" nowrap">5</td>
    <td class=""><i>NULL</i></td>
</tr>
            
    <tr class="even">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">hier6</td>
    <td class="">ref</td>
    <td class="">loc_id</td>
    <td class="">loc_id</td>
    <td class="">4</td>
    <td class="">db.x.hier6</td>
    <td align="right" class=" nowrap">5</td>
    <td class=""><i>NULL</i></td>
</tr>
            
    <tr class="odd">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">hier7</td>
    <td class="">ref</td>
    <td class="">loc_id</td>
    <td class="">loc_id</td>
    <td class="">4</td>
    <td class="">db.x.hier7</td>
    <td align="right" class=" nowrap">5</td>
    <td class=""><i>NULL</i></td>
</tr>
            
    <tr class="even">
    <td align="right" class=" nowrap">1</td>
    <td class="">SIMPLE</td>
    <td class="">hier8</td>
    <td class="">ref</td>
    <td class="">loc_id</td>
    <td class="">loc_id</td>
    <td class="">4</td>
    <td class="">db.x.hier8</td>
    <td align="right" class=" nowrap">5</td>
    <td class=""><i>NULL</i></td>
</tr>
            
</tbody>
</table>

Profiling

Starting    7 µs
Waiting For Query Cache Lock    2 µs
Checking Query Cache For Query  9 µs
Checking Privileges On Cached   2 µs
Checking Permissions    3 µs
Checking Permissions    1 µs
Checking Permissions    1 µs
Checking Permissions    1 µs
Checking Permissions    1 µs
Checking Permissions    1 µs
Checking Permissions    1 µs
Checking Permissions    1 µs
Sending Cached Result To Clien  27 µs
Logging Slow Query  2 µs
Cleaning Up 1 µs
mysql


Solution 1:[1]

When profiling, always use the SQL_NO_CACHE key word to disable returning cached query results.

SELECT SQL_NO_CACHE DISTINCT x.loc_id AS id_a,
...

To improve query performance, narrow the result set (by applying criteria in the WHERE clause), and ensure you have an index on locations.loc_id and geo_xref.oid.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions