Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a monster query, which runs quite quick in many cases, but in some cases very much slower.

The EXPLAIN output below is from one of the slow cases.

I'm guessing that the slowdown is related to the very first row, the <derived2> table, which the fast queries do not use; my knowledge however, is limited. For one, I'm curious why that row pops up some times and not others.

To an experienced eye, what stands out here? What might be the first steps in speeding up this query?

+----+-------------+-------------------+--------+---------------+--------------+---------+------------------------------------+--------+----------------+
| id | select_type | table             | type   | possible_keys | key          | key_len | ref                                | rows   | Extra          |
+----+-------------+-------------------+--------+---------------+--------------+---------+------------------------------------+--------+----------------+
|  1 | PRIMARY     | <derived2>        | ALL    | NULL          | NULL         | NULL    | NULL                               | 322477 |                |
|  1 | PRIMARY     | o                 | eq_ref | uuid          | uuid         | 32      | pr.offering_uuid                   |      1 |                |
|  1 | PRIMARY     | pn                | eq_ref | uuid          | uuid         | 30      | brain.o.partnumber_uuid            |      1 |                |
|  1 | PRIMARY     | p                 | eq_ref | uuid          | uuid         | 32      | brain.o.product_uuid               |      1 |                |
|  1 | PRIMARY     | os                | eq_ref | uuid          | uuid         | 108     | brain.o.offering_source_uuid       |      1 | Using index    |
|  1 | PRIMARY     | b                 | eq_ref | uuid          | uuid         | 32      | brain.p.company_uuid_brand         |      1 |                |
|  1 | PRIMARY     | bn                | eq_ref | PRIMARY       | PRIMARY      | 3       | brain.b.company_name_id_legal_name |      1 |                |
|  1 | PRIMARY     | <derived4>        | ALL    | NULL          | NULL         | NULL    | NULL                               |  39895 |                |
|  1 | PRIMARY     | pmr               | ALL    | NULL          | NULL         | NULL    | NULL                               |     51 |                |
|  1 | PRIMARY     | pm                | ALL    | NULL          | NULL         | NULL    | NULL                               |    215 |                |
|  4 | DERIVED     | c                 | ALL    | NULL          | NULL         | NULL    | NULL                               |  39895 | Using filesort |
|  4 | DERIVED     | ct                | ref    | company_uuid  | company_uuid | 108     | brain.c.uuid                       |      1 | Using index    |
|  2 | DERIVED     | <derived3>        | ALL    | NULL          | NULL         | NULL    | NULL                               | 322477 | Using where    |
|  3 | DERIVED     | m3_offering_price | ALL    | NULL          | NULL         | NULL    | NULL                               | 322477 | Using filesort |
+----+-------------+-------------------+--------+---------------+--------------+---------+------------------------------------+--------+----------------+
share|improve this question
    
7 full tables scans so you should index.. you should also provide us with an "show create table your_table" output –  Raymond Nijland Oct 30 '13 at 21:57
    
Thanks! I was able to massively speed up the query by converting the derived tables into temporary tables with indexes. Make your comment an answer, and I'll accept it! –  David Oct 31 '13 at 23:09
    
i think your should post your query, show create table and new EXPLAIN output and use profiling on that query and post the output i may be able the improve your query even more. –  Raymond Nijland Nov 1 '13 at 0:02
    
Thanks! I appreciate the offer. I'm curious to learn and to see what you could do! When I get a little less busy I might come back and add the info. –  David Nov 1 '13 at 21:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.