使用具有连接的子查询计算行索引，结果 A*B 检查行 [英] Calculating row indices with subquery having joins, results in A*B examined rows

问题描述

这个问题源自我之前开始的一个问题:所有连接都被视为嵌套循环连接.

<块引用>

MySQL 使用嵌套循环连接方法解析所有连接.这意味着 MySQL 从第一个表中读取一行，然后在第二个表中找到匹配的行，第三个表，依此类推.

所以要回答您的问题...不，您将无法倒计时.但是，通过向连接列添加索引，您可以获得更快的结果，但行数将保持不变.

This question is derived from a one I started previously: Incorrect row index when grouping

Due to different natures, I'm asking here and will provide the answer back there once I have resolved this issue.

---

I thought about subqueries, and came up with this:

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

Which actually works, I get the desired result:

+----+--------+------------------------------------+---------------------+---------+
| 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 |
+----+--------+------------------------------------+---------------------+---------+

The problem now is... I ran a EXPLAIN query, to see how fast it works. And, I have a number there that is really bugging me:

Well, the number is obvious: 252 * 1663 = 419076.

This worries me, though - is the row count normal there, or I have to optimize the query? And if so, then how do I optimize this one?

解决方案

As of MySQL version 5.7 all joins are treated as nested loop joins.

MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on.

So to answer your question... no, you won't be able to get that row count down. However, by adding indexes to your join columns you may be able to achieve faster results but your row count will be the same.

这篇关于使用具有连接的子查询计算行索引，结果 A*B 检查行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！