mysql - MySQL查询效率(使用MIN)

有人可以帮助我优化此查询吗？我是Stackoverflow的新手，并感谢任何想法。

-

SELECT COUNT(DISTINCT(g2.serialid)) as countResult
from g2table g2 INNER JOIN
     ((SELECT p.serialid AS serialid, p.created as created, MIN(g.starttime) AS firstuse
       FROM gtable g INNER JOIN
            ptable p
            ON g.serialid = p.serialid
       WHERE  (p.created < '2015-05-22')
       GROUP BY p.serialid
      ) ) AS firstuseset
     ON g2.serialid = firstuseset.serialid
 WHERE (firstuseset.firstuse < '2015-05-22') AND
       ((g2.starttime > '2015-05-22') AND (g2.starttime < '2015-05-23'))

最佳答案

为了优化MIN(g.starttime)，我认为您需要(serialid, starttime)中gtable上的复合索引：

CREATE INDEX serial_starttime on gtable (serialid, starttime);

created中ptable上的索引将优化WHERE子句。