我需要优化一个MySQL查询。我有一个少于100.000条记录的“投注”表。我需要显示当月的所有记录。
SELECT betting.id
FROM betting, members
WHERE members.status = 1
and members.id = betting.user
and betting.date between '2016-09-01' and '2016-09-30'
and betting.status = 1
and betting.type = 1;
当我尝试解释命令时:
SIMPLE betting range user,date,type,type_2 type_2 7 NULL 4966 Using where
SIMPLE members eq_ref PRIMARY PRIMARY 4 betting.user 1 Using where
有没有机会减少数据库搜索?问题是显示此结果的页面调用过多,从而导致大量CPU开销。不幸的是,我无法缓存结果,因为它们需要实时。有任何想法吗 ?
最佳答案
这是您的查询,其格式易于理解(至少对我而言):
SELECT b.id
FROM betting b join
members m
ON m.id = b.user
WHERE m.status = 1
b.date between '2016-09-01' and '2016-09-30' and
b.status = 1 and
b.type = 1;
我认为最好的索引策略是:
betting(status, type, date, user)
和members(id, status)
。