MySQL查询优化帮助

Question

希望您能帮助我在正确的轨道上开始优化我的查询。我以前从来没有想过太多关于优化的问题，但是我有一些类似于下面的查询，并且想开始集中精力提高它们的效率。我急需优化的查询示例如下:

SELECT COUNT(*) AS `records_found` 

FROM (`records_owners` AS `ro`, `records` AS `r`) 

WHERE r.reg_no = ro.contact_no 

AND `contacted_email` <> "0000-00-00" 

AND `contacted_post` <> "0000-00-00" 

AND `ro`.`import_date` BETWEEN "2010-01-01" AND "2010-07-11" AND `r`.`pa_date_of_birth` > "2010-01-01" AND EXISTS ( SELECT `number` FROM `roles` WHERE `roles`.`number` = r.`reg_no` )

在上面运行 EXPLAIN 会产生以下结果:

| id | select_type        | table | type   | possible_keys | key     | key_len | ref                                   | rows  | Extra       |

+----+--------------------+-------+--------+---------------+---------+---------+---------------------------------------+-------+-------------+

|  1 | PRIMARY            | r   | ALL    | NULL          | NULL    | NULL    | NULL                                  | 21533 | Using where | 

|  1 | PRIMARY            | ro  | eq_ref | PRIMARY       | PRIMARY | 4       |          r.reg_no |     1 | Using where | 

|  2 | DEPENDENT SUBQUERY | roles  | ALL    | NULL          | NULL    | NULL    | NULL                                  |   189 | Using where | 

Answer 1

如您所见，您有一个依赖子查询，这是 MySQL 中性能最差的事情之一。请参阅此处获取提示:

http://dev.mysql.com/doc/refman/5.0/en/select-optimization.html

http://dev.mysql.com/doc/refman/5.0/en/in-subquery-optimization.html