mysql count查询 a表left join b表和b表left join a表查询速度差别很大

Question

ims_customer 数据1662

ims_customer_bind_user 数据1612

sql1:
SELECT count(1) from(
SELECT a.id AS "id"
FROM ims_customer a
left join ims_customer_bind_user cbu on cbu.customer_id=a.id
WHERE a.del_flag = 0
and (cbu.del_flag = 0 or cbu.del_flag is null)
ORDER BY a.update_date
) as ww

sql2:
SELECT count(1) from(
SELECT a.id AS "id"
FROM ims_customer_bind_user cbu
left join ims_customer a on cbu.customer_id=a.id
WHERE a.del_flag = 0
and (cbu.del_flag = 0 or cbu.del_flag is null)
ORDER BY a.update_date
) as ww

sql3:
SELECT a.id AS "id"
FROM ims_customer a
left join ims_customer_bind_user cbu on cbu.customer_id=a.id
WHERE a.del_flag = 0
and (cbu.del_flag = 0 or cbu.del_flag is null)
ORDER BY a.update_date
DESC limit 10

sql4:
SELECT a.id AS "id"
FROM ims_customer_bind_user cbu
left join ims_customer a on cbu.customer_id=a.id
WHERE a.del_flag = 0
and (cbu.del_flag = 0 or cbu.del_flag is null)
ORDER BY a.update_date
DESC limit 10

有索引时
sql1: 2s
sql2: 15ms
sql3: 23ms
sql4: 46ms
没索引时
sql1: 900ms
sql2: 14ms
sql3: 1.8s
sql4: 50ms
count查询速度差别很大，为什么？

Answer 1

还在问。。。在你前面问的一个问题中说了count的时候不需要用order by，也不要用派生表！还有a left join b 跟 b left join a意义完全不一样，如果a与b表是一一对应的就用inner join