我需要优化一个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)

10-06 11:38