慢MySQL查询

Question

据我所知，我已经尽力使这个查询更快了。每个表的相同引擎，用于联接、order或where子句的所有字段的索引。

问题似乎是表aam和ag没有使用索引，尽管有可用的索引。

查询：

    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

解释(抱歉，格式化搞砸了)：使用这个图像而不是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

以下是数据库http://pastebin.com/Rbyrj6x3的相关结构

编辑我已经对查询做了一个概要文件：Copying to tmp table真的很慢。

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

所有表的所有字段都有外键吗？请描述一下你们的桌子

Answer 2

尝试将agent.status上的索引转换为覆盖索引，方法是添加agent_id作为第二个组件：

create index idx2 on agent( status, id)

这可能使查询在不得不从数据表检索记录之前消除更多的代理行。