select count(*) from lg_mailorder m left join sc_sale_trade t on m.tradeid=t.id where m.enterpriseid='KH' AND m.status=1 AND m.deleteflag=0 AND t.truckType=2
单表count就快
两表count非常慢,要30秒
100万数据
求解决方案
谢谢
...全文
435314打赏收藏
select count(*)两表关联时,这两个表都有100万数据,非常慢
select count(*) from lg_mailorder m left join sc_sale_trade t on m.tradeid=t.id where m.enterpriseid='KH' AND m.status=1 AND m.deleteflag=0 AND t.truckType=2 单表count就快 两表count非常慢,要30秒 100万数据 求解决方案 谢谢
SELECT
COUNT (*),
t.truckType
FROM
lg_mailorder m
LEFT JOIN sc_sale_trade t ON m.tradeid = t.id
WHERE
m.enterpriseid = 'KH'
AND m.status = 1
AND m.deleteflag = 0
GROUP BY t.truckType
HAVING t.truckType = 2
回复下各位:
select count(*) from lg_mailorder m left join sc_sale_trade t on m.tradeid=t.id where m.enterpriseid='KH' AND m.status=1 AND m.deleteflag=0 AND t.truckType=2
如果把 AND t.truckType=2去掉就很快,如果没去,就不走联合索引
t.truckType是第二个表的索引
这种情况怎么办
结果级是100万数据量,还是这两个表都是100w,相关字段有索引不?
select count(*) from lg_mailorder m left join sc_sale_trade t on m.tradeid=t.id AND t.truckType=2 where m.enterpriseid='KH' AND m.status=1 AND m.deleteflag=0