MySQL query optimisation help

Question

Hi guys, hoping you can help me on the right track to start optimising my queries. I've never thought too much about optimisation before, but I have a few queries similar to the one below and want to start concentrating on improving their efficiency. An example of a query which I badly need to optimise is as follows:

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` )

Running EXPLAIN on the above produces the following:

| 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

As you can see, you have a dependent subquery, which is one of the worst thing performance-wise in MySQL. See here for tips:

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