mysql - 优化慢查询

Question

我不擅长优化 SQL，但我需要此查询尽可能快地运行。它将通过 PHP 脚本在大约 40 个不同的数据库上重复运行。 PHP 然后按时间顺序对结果进行排序。

SELECT DATE_FORMAT(lastmoddate, '%a %b %D, %Y  %l:%i%p') as lastmod,
       count(*) as num_modified,
       (select count(*) from store.item) as totalitems,
       UNIX_TIMESTAMP(lastmoddate) as timestamp
FROM store.item
group by lastmoddate
HAVING lastmoddate = MAX(lastmoddate)
order by lastmoddate desc
limit 1;

解释

• lastmoddate为条目记录的最后修改日期
• count(*) as num_modified 是上次更新时修改的项目数
• 子查询获取表中的项目总数
• UNIX_TIMESTAMP 可能是不必要的，但我稍后会用它在 PHP 中进行排序。该结果与其他数据库的结果汇集在一起​​，然后进行排序。

因此最终目标是获取一个表，其中包含最后修改日期、最后修改日期修改的项目数以及数据库中的项目总数。

子查询可能会被删除，但它似乎不会对速度产生太大影响。需要其他结果。如果 DATE_FORMAT 或 UNIX_TIMESTAMP 很慢，它们可以被删除，但它们会被 PHP 中可能同样慢的东西替换。 (就像我说的，我对优化 SQL 没有任何了解。我感觉很好，只是得到一个有效的查询。)

MySQL 5.5 和 PHP 5.3

Answer 1

您正在尝试获取有关具有最大 lastmoddate 的行的信息。您的查询按 lastmoddate 聚合所有数据，然后选择最后一行。

相反，先进行过滤，然后进行聚合。您只会在输出中获得一行，因此您甚至不需要 group by 子句。

该方法是计算子查询中的最大最后修改日期和总项目数。这是加入的，因此可以过滤数据:

select DATE_FORMAT(i.lastmoddate, '%a %b %D, %Y  %l:%i%p') as lastmod,
       COUNT(*) as NumModified,
       const.totalitems,
       UNIX_TIMESTAMP(i.lastmoddate) as timestamp
from store.item i cross join
     (select max(lastmoddate) as maxlastmoddate, count(*) as totalitems
     ) const
where i.lastmoddate = const.maxlastmoddate;

store.item(lastmoddate) 上的索引将进一步提高您的性能。