Slow MySQL Query

Question

I have tried everything to make this query faster that I know of. Same engine for every table, indexes on all the fields which are used for joins, order by or where clauses.

The problem seems to be tables aam and ag are not using an index despite there being indexes available.

Query:

    SELECT DISTINCT `a`.`id`, `a`.`full_name`, `a`.`rating`, `a`.`licence`, `a`.`licence_issued`, `ag`.`name` as agency_name
FROM (`property_suburb_map` psm)
JOIN `campaign_property_map` cpm ON `psm`.`property_id` = `cpm`.`property_id`
JOIN `campaign` c ON `cpm`.`campaign_id` = `c`.`id`
JOIN `campaign_agent_map` cam ON `cpm`.`campaign_id` = `cam`.`campaign_id`
JOIN `agent` a ON `cam`.`agent_id` = `a`.`id`
JOIN `agency_agent_map` aam ON `aam`.`agent_id` = `a`.`id`
JOIN `agency` ag ON `aam`.`agency_id` = `ag`.`id`
WHERE `c`.`closing_date` >= '2009-10-12'
AND `psm`.`suburb_id` = '5911'
AND `a`.`status` = 'Active'
ORDER BY `a`.`rating` DESC, `a`.`full_name`
LIMIT 12

Explain (sorry, formatting got messed up): Use this image instead http://imgur.com/UzSpC.jpg

id  select_type     table           type    possible_keys   key     key_len     ref     rows    Extra

1   SIMPLE          a               ref     PRIMARY,status  status  1   const   790     Using where; Using temporary; Using filesort
1   SIMPLE          aam             ref     agency_id_2,
                                            agent_id,
                                            agency_id       agent_id      4     hindsight.a.id  1    
1   SIMPLE          ag              eq_ref  PRIMARY     PRIMARY     4   hindsight.aam.agency_id     1    
1   SIMPLE          cam             ref     agent_id,
                                            campaign_id     agent_id    4   hindsight.a.id  9   Distinct
1   SIMPLE          c               eq_ref  PRIMARY,closing_date    PRIMARY     4   hindsight.cam.campaign_id   1   Using where; Distinct
1   SIMPLE          cpm             ref     campaign_id     campaign_id     4   hindsight.c.id  1   Using where; Using index; Distinct
1   SIMPLE          psm             ref     property_id,suburb_id   property_id     4   hindsight.cpm.property_id   1   Using where; Distinct

Here is the relevant structure of the database http://pastebin.com/Rbyrj6x3

Edit I have done a profile on the query: Copying to tmp table is really slow.

mysql> show profile for query 6;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000000 |
| Opening tables       | 0.000000 |
| System lock          | 0.000000 |
| Table lock           | 0.000000 |
| init                 | 0.000000 |
| optimizing           | 0.000000 |
| statistics           | 0.000000 |
| preparing            | 0.000000 |
| Creating tmp table   | 0.000000 |
| executing            | 0.000000 |
| Copying to tmp table | 0.112000 |  <- WTF!
| Sorting result       | 0.004000 |
| Sending data         | 0.000000 |
| end                  | 0.000000 |
| removing tmp table   | 0.000000 |
| end                  | 0.000000 |
| query end            | 0.000000 |
| freeing items        | 0.000000 |
| logging slow query   | 0.000000 |
| cleaning up          | 0.000000 |
+----------------------+----------+
20 rows in set (0.00 sec)

Answer 1

Do you have foreign keys on all fields of all tables? Please describe your tables

Answer 2

Try turning the index on agent.status into a covering index by adding agent_id as a second component:

create index idx2 on agent( status, id)

This might enable the query to eliminate more of the agent rows before having to retrieve records from the data table.