执行count（）计算减慢我的mysql查询？ [英] Is performing a count() calculation slowing down my mysql query?

问题描述

我还在学习MySQL。我可能是一个非常基本的错误，我准备在这里...在这里... ...

I'm still learning about MySQL. I may be making a very basic error, and I'm prepared to be chastened here...

这个查询尝试做的是从我们的网站根据他们已经做的书和食谱评论的数量的计数。

What this query is trying to do is select the top members from our website based on a count of the number of book and recipe reviews they have made.

我正在计算SQL查询本身的总计。查询慢（9秒），肯定不会扩展考虑到我们只有400个成员和几千评论到目前为止，它的增长很快。

I'm making a calculation of the total in the SQL query itself. The query is slow (9 seconds) and will definitely not scale considering we only have 400 members and a few thousand reviews so far and it's growing quite quickly.

我认为它在这里做一个全表扫描，计算是减慢，但我不知道一个替代的方法，这样做，并会爱一些智慧。

I presume it's doing a full table scan here, and that the calculation is slowing it down, but I don't know of an alternative way to do this and would love some wisdom.

这里是SQL语句：

SELECT users.*, COUNT( DISTINCT bookshelf.ID ) AS titles, COUNT( DISTINCT book_reviews.ID ) as bookreviews, COUNT( DISTINCT recipe_reviews.ID ) AS numreviews, COUNT( DISTINCT book_reviews.ID ) + COUNT( DISTINCT recipe_reviews.ID ) as reviewtotal
FROM users
LEFT OUTER JOIN recipe_reviews ON recipe_reviews.user_id = users.ID
LEFT OUTER JOIN book_reviews ON book_reviews.user_id = users.ID
LEFT OUTER JOIN bookshelf ON users.ID = bookshelf.user_id
GROUP BY users.ID
ORDER BY reviewtotal DESC
LIMIT 8

这是说明：

+----+-------------+----------------+-------+-------------------+-------------------+---------+---------------------+------+---------------------------------+
| id | select_type | table          | type  | possible_keys     | key               | key_len | ref                 | rows | Extra                           |
+----+-------------+----------------+-------+-------------------+-------------------+---------+---------------------+------+---------------------------------+
|  1 | SIMPLE      | users          | index | NULL              | PRIMARY           | 4       | NULL                |  414 | Using temporary; Using filesort | 
|  1 | SIMPLE      | recipe_reviews | ref   | recipe_reviews_fk | recipe_reviews_fk | 5       | users.ID            |   12 |                                 | 
|  1 | SIMPLE      | book_reviews   | ref   | user_id           | user_id           | 5       | users.ID            |    4 |                                 | 
|  1 | SIMPLE      | bookshelf      | ref   | recipe_reviews_fk | recipe_reviews_fk | 5       | users.ID            |   13 |                                 | 
+----+-------------+----------------+-------+-------------------+-------------------+---------+---------------------+------+---------------------------------+

UPDATE&已解决：

我意识到，并且@recursive确认，查询是问题的根源。我从这里得到笛卡尔产品。我将其重写为一系列子查询，最后的工作代码在这里：

I realized, and @recursive confirmed, that the query is the root of the problem. I'm getting Cartesian products from this. I rewrote it as a series of subqueries and the final working code is here:

SELECT  *, bookreviews + recipereviews AS totalreviews
FROM (SELECT users.*,
            (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
            (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews,
            (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews
    FROM users) q

这给我一个结果，以毫秒为单位。还有一些方法可以通过JOIN来实现。请参见如何将几个子查询的结果添加在一起？如果你想跟着这个。

This gives me a result in milliseconds. There are also ways to do this with JOINs. See How to add together the results of several subqueries? if you want to follow this up.

推荐答案

您可以尝试查看是否从删除 DISTINCT 修饰符。假设 DISTINCT ed字段仍然是主键，这可能会导致不必要的工作。

You might try seeing if there is an improvement from removing the DISTINCT modifiers. Assuming the DISTINCTed fields are primary keys anyway, this could be causing unnecessary work.

这篇关于执行count（）计算减慢我的mysql查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！