Php mysql 查询慢_php – 使用COUNT进行子查询的慢MYSQL查询

对,我不知道为什么但是这个查询执行时间超过6秒,索引的设置都正确,如果我单独运行每个查询,它运行时间不到0.5秒就可以执行.

这是查询

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

Explain查询的结果如下

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

计数查询所在的原因是因为我需要知道这些表中的行数,这不能在另一个查询中完成,因为结果也需要按这些值排序:(