分组时不正确的行索引 [英] Incorrect row index when grouping

问题描述

我构建了以下查询:

SELECT
    p.id,
    p.tag_id,
    p.title,
    p.created_at,
    @indexer := @indexer + 1 AS indexer
FROM 
    `posts` AS p
LEFT JOIN
    `votes` AS v
        ON p.id = v.votable_id
        AND v.votable_type = "Post"
        AND v.deleted_at IS NULL
JOIN
    (SELECT @indexer := 0) AS i
WHERE
    p.deleted_at IS NULL
GROUP BY
    p.id

我得到的结果是:

+----+--------+------------------------------------+---------------------+---------+
| id | tag_id |               title                |     created_at      | indexer |
+----+--------+------------------------------------+---------------------+---------+
|  2 |      2 | PostPostPost                       | 2014-10-23 23:53:15 |     248 |
|  3 |      3 | Title                              | 2014-10-23 23:56:13 |       6 |
|  4 |      2 | GIFGIFIGIIF                        | 2014-10-23 23:59:03 |    1316 |
|  5 |      2 | GIFGIFIGIIF                        | 2014-10-23 23:59:03 |    1317 |
|  6 |      4 | My new avatar                      | 2014-10-26 22:22:30 |    1318 |
|  7 |      5 | Hi, haiii, oh Hey !                | 2014-10-26 22:38:10 |       1 |
|  8 |      6 | Mclaren testing stealth technology | 2014-10-26 22:44:15 |       5 |
|  9 |      7 | Just random thoughts while pooping | 2014-10-26 22:50:03 |       2 |
+----+--------+------------------------------------+---------------------+---------+

然而，我期待这个:

+----+--------+------------------------------------+---------------------+---------+
| id | tag_id |               title                |     created_at      | indexer |
+----+--------+------------------------------------+---------------------+---------+
|  2 |      2 | PostPostPost                       | 2014-10-23 23:53:15 |       1 |
|  3 |      3 | Title                              | 2014-10-23 23:56:13 |       2 |
|  4 |      2 | GIFGIFIGIIF                        | 2014-10-23 23:59:03 |       3 |
|  5 |      2 | GIFGIFIGIIF                        | 2014-10-23 23:59:03 |       4 |
|  6 |      4 | My new avatar                      | 2014-10-26 22:22:30 |       5 |
|  7 |      5 | Hi, haiii, oh Hey !                | 2014-10-26 22:38:10 |       6 |
|  8 |      6 | Mclaren testing stealth technology | 2014-10-26 22:44:15 |       7 |
|  9 |      7 | Just random thoughts while pooping | 2014-10-26 22:50:03 |       8 |
+----+--------+------------------------------------+---------------------+---------+

提示: 问题在于 indexer 字段.它发生在添加 GROUP BY 语句之后.

Hint: The problem is with indexer field. It happened after adding the GROUP BY statement.

我尝试移动 JOIN (SELECT @indexer := 0) AS i ，将其更改为简单的额外选择，但没有任何更改.无论如何，索引都搞砸了.

I have tried moving the JOIN (SELECT @indexer := 0) AS i around, changing it to simply an extra select, but no changes. The indices are screwed up anyways.

我该如何解决这个问题，以便索引正确?
这个问题背后的原因是什么?

How do I fix this, so that the indexes are correct?
And what is the reasoning behind this problem?

推荐答案

自己找到了答案，不得不将查询分成多个子查询.

Found the answer myself, had to separate the query into multiple subqueries.

SELECT
    mq.*,
    @indexer := @indexer + 1 AS indexer
FROM
(
    SELECT
        p.id,
        p.tag_id,
        p.title,
        p.created_at
    FROM 
        `posts` AS p
    LEFT JOIN
        `votes` AS v
            ON p.id = v.votable_id
            AND v.votable_type = "Post"
            AND v.deleted_at IS NULL
    WHERE
        p.deleted_at IS NULL
    GROUP BY
        p.id
) AS mq
JOIN
    (SELECT @indexer := 0) AS i

显然，这导致了查询的大量访问行(根据 EXPLAIN)，但也设法通过额外的索引解决了这个问题.可以在此处找到该问题的完整答案:使用具有连接的子查询计算行索引，结果为 A*B 检查行

Apparently, that caused huge amount of accessed rows for the query (as per EXPLAIN), but managed to fix that too with extra indices. The full answer to that problem can be found here: Calculating row indices with subquery having joins, results in A*B examined rows

这篇关于分组时不正确的行索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！