使用 HAVING 子句进行慢查询 - 我可以加快速度吗? [英] Slow query with HAVING clause - can I speed it up?

问题描述

我有以下查询，它产生了预期的结果，但速度很慢(大约需要 10 秒.gstats 表在我的开发环境中大约有 130k 行，并且在生产中更大):

I have the following query which produces the expected results but is very slow (it takes about 10 seconds. The gstats table has about 130k rows in my development environment and is much bigger in production):

SELECT count(d.id) AS dcount, s.id, s.name
FROM sites s
LEFT JOIN deals d ON (s.id = d.site_id AND d.is_active = 1)
WHERE (s.is_active = 1)
AND s.id IN(
    SELECT g.site_id
    FROM gstats g
    WHERE g.start_date > '2015-04-30'
    GROUP BY g.site_id
    HAVING SUM(g.results) > 100
)
GROUP BY s.id
ORDER BY dcount ASC

我做错了吗?我怎样才能加快速度?

Am I doing something wrong? How can I speed this up?

添加索引/使用视图有帮助吗?

Would adding indexes/using a view help?

推荐答案

一个快速的解决方法是在子查询中过滤:

A quick fix would be to filter in the subquery:

SELECT count(d.id) AS dcount, s.id, s.name
FROM sites s
LEFT JOIN deals d ON (s.id = d.site_id AND d.is_active = 1)
WHERE (s.is_active = 1)
AND s.id IN(
    SELECT g.site_id
    FROM gstats g
    WHERE g.start_date > '2015-04-30' AND g.site_id = s.id
    GROUP BY g.site_id
    HAVING SUM(g.results) > 100
)
GROUP BY s.id
ORDER BY dcount ASC

否则，您将对每个可能的候选人进行这样的分组查询.我们可以用 EXISTS 让它更优雅:

Since otherwise, you do such grouping query for every possible candidate. We can make this more elegant with EXISTS:

SELECT count(d.id) AS dcount, s.id, s.name
FROM sites s
LEFT JOIN deals d ON (s.id = d.site_id AND d.is_active = 1)
WHERE (s.is_active = 1)
AND EXISTS (
    SELECT 1
    FROM gstats g
    WHERE g.site_id = s.id AND g.start_date > '2015-04-30'
    HAVING SUM(g.results) > 100
)
GROUP BY s.id
ORDER BY dcount ASC

但我们还没有完成，现在我们将为每个元素使用EXISTS.这很奇怪，因为查询只依赖于 s.id，所以它只依赖于 group，而不是单个行.所以潜在加速，但这取决于表的大小等，是将条件移动到HAVING语句:

But we are not done yet, now we will use the EXISTS for every element. That's weird since the query only depends on s.id, so it only depends on the group, not the individual rows. So a potential speedup, but this depends on the sizes of the tables, etc. is to move the condition to a HAVING statement:

SELECT count(d.id) AS dcount, s.id, s.name
FROM sites s
LEFT JOIN deals d ON (s.id = d.site_id AND d.is_active = 1)
WHERE (s.is_active = 1)
GROUP BY s.id
ORDER BY dcount ASC
HAVING EXISTS (
    SELECT 1
    FROM gstats g
    WHERE g.site_id = s.id AND g.start_date > '2015-04-30'
    HAVING SUM(g.results) > 100
)

这篇关于使用 HAVING 子句进行慢查询 - 我可以加快速度吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！