MySQL计数优化

Question

我希望能够减少一个mysql count查询的加载时间，目前这个查询需要2.5秒的时间。查询中所有列都有索引。

SELECT COUNT(1)
FROM song AS s
JOIN song_text AS st
  ON(st.song_id = s.song_id)
JOIN phpfox_user AS u
  ON(u.user_id = s.user_id)
WHERE st.lyrics LIKE '%a%' AND s.is_active = 1 AND s.public = 1

查询返回的行加载时间为0.0009060秒。

SELECT s.*, st.lyrics, u.first_name
FROM song AS s
JOIN song_text AS st
  ON(st.song_id = s.song_id)
JOIN phpfox_user AS u
  ON(u.user_id = s.user_id)
WHERE st.lyrics LIKE '%a%' AND s.is_active = 1 AND s.public = 1
ORDER BY s.date_added DESC 
LIMIT 12

为什么计数查询的加载时间比返回行的查询要长得多？有什么方法可以减少计数查询的加载时间，使其与其他查询的时间相似？

- WeekendCoder

---

你尝试过在查询上运行EXPLAIN吗？这应该会给你一些见解。我相信@AdityaNaidu可能是正确的 - 第二个查询中的LIMIT可能会过滤掉第一个查询中需要的大部分工作。 - evan

从第二个查询中删除LIMIT子句并发布查询时间。使用SELECT SQL_NO_CACHE <rest of your query>从方程式中删除缓存。 - Salman A

将第二个查询的限制移除后，查询时间为7.6967690秒。 - WeekendCoder

去掉排序和限制后，加载时间变为5.0759008秒。我想知道的问题是如何将计数查询时间减少到小于0.5秒。 - WeekendCoder

Answer 1

你在意计数中的u.first_name值吗？如果不在意，可以移除第二个join，因为它似乎没有增加任何价值。

此外，尝试从歌曲表中使用count(s.song_id)而不是count(1)。我不确定这是否是真正的优化还是我的想象。

- DwB

Answer 2

第二个查询语句在获取前12条记录之后就完成了。而您的第一个（计数）查询必须连接所有表中的所有列。

这是我能看到的唯一区别！

- Aditya Naidu

Answer 3

我认为这是因为

LIMIT 12

你只捕获了你的选择语句中的12行。

- frugi

Answer 4

首先，必须查看查询的执行计划。

我认为第一个查询中的临时表相当大，因此聚合应用于大量数据。

另一方面，LIMIT 12已经告诉查询处理器不需要处理所有行，而只需处理12行，因此临时表很小。

- Timofey