Right I have no idea why but this query takes well over 6 seconds to execute, index's are all setup correctly and if I run each query separately it works great with less than 0.5 seconds to execute.
Here is the query
SELECT c.supplier_id, supplier_name, address1, address2, address3, address4, suppliertype, postcode, contact_name,
(SELECT COUNT(*)
FROM supplier_questions q1
WHERE c.supplier_id = q1.supplier_id AND q1.incomplete = '0') AS questions,
IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated,
(SELECT COUNT(*)
FROM supplier_questions q2
WHERE c.supplier_id = q2.supplier_id AND q2.reviewed = '1') AS reviewed,
questapproved,
ss.supplier_no AS supplier_no
FROM suppliers c
INNER JOIN supplier_site ss ON c.supplier_id = ss.supplier_id
WHERE c.supplier_id != '0' AND ss.site_id = '2'
GROUP BY c.supplier_id
ORDER BY c.supplier_name ASC
LIMIT 0, 20
Results of the Explain query is as follows
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ss ref site_id,supplier_id site_id 4 const 1287 Using where; Using temporary; Using filesort
1 PRIMARY c eq_ref PRIMARY PRIMARY 4 ss.supplier_id 1
3 DEPENDENT SUBQUERY q2 ref supplier_id,reviewed reviewed 4 const 263 Using where
2 DEPENDENT SUBQUERY q1 ref supplier_id,incomplete incomplete 4 const 254 Using where
The reason the count queries are in there is because I need to know the number of rows from those tables, this can't be done in another query as the results also need to be sorted by those values :(