我正在努力优化这个慢查询(>2秒)
SELECT COUNT(*)
FROM crmentity c, mdcalls_trans_activity_update mtu, mdcalls_trans mt
WHERE (mtu.dept = 'GUN' OR mtu.dept = 'gun') AND
mtu.trans_code = mt.trans_code AND
mt.activityid = c.crmid AND
MONTH(mtu.ts) = 2 AND
YEAR(mtu.ts) = YEAR(NOW()) AND
c.deleted = 0 AND
c.smownerid = 28
当我使用EXPLAIN时,以下为输出结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c index_merge PRIMARY,crmentity_smownerid_idx,crmentity_deleted_smownerid_idx,crmentity_smownerid_deleted_idx crmentity_smownerid_idx,crmentity_deleted_smownerid_idx 4,8 NULL 91 Using intersect(crmentity_smownerid_idx,crmentity_deleted_smownerid_idx); Using where; Using index
1 SIMPLE mt ref activityid activityid 4 pharex.c.crmid 60
1 SIMPLE mtu ref dept_idx dept_idx 5 const 1530 Using where
我创建了一个索引(dept_idx),但是对于包含1,380,384条记录的数据集,仍需要超过2秒才能运行该查询。是否有其他更优的方式来表达此查询?
更新:根据David的建议,现在该查询只需要几毫秒就能执行完毕,而不是之前需要超过2秒(实际上,在MySQL 5.0版本上需要51秒)。
WHERE lower(mtu.dept) = 'gun' AND ...
但我猜你的数据库已经优化过了。 - initall