哪个MySQL查询更快？ [英] Which MySQL Query is faster?

问题描述

哪个查询会执行得更快，哪个是完美的查询？

Which query will execute faster and which is perfect query ?

SELECT
    COUNT(*) AS count
FROM
    students
WHERE
    status = 1
AND
    classes_id IN(
                    SELECT
                        id
                    FROM
                        classes
                    WHERE
                        departments_id = 1
                );

或

SELECT
    COUNT(*) AS count
FROM
    students s
LEFT JOIN
    classes c
ON
    c.id = s.classes_id
WHERE
    status = 1
AND
    c.departments_id = 1

我已经放置两个查询都将输出相同的结果。现在我想知道哪个方法执行得更快，哪个方法是正确的方法？

I have placed two queries both will output same result. Now I want to know which method will execute faster and which method is correct way ?

推荐答案

不幸的是，MySQL会执行您的子查询作为一个DEPENDENT QUERY，这意味着子查询将为外部查询中的每一行运行。你会认为MySQL会聪明到足以检测子查询不是一个相关的子查询，并且只运行一次，唉，它还不是很聪明。

Unfortunately, MySQL will execute your subquery as a DEPENDENT QUERY, which means that the subquery will be ran for each row in the outer query. You'd think MySQL would be smart enough to detect that the subquery isn't a correlated subquery and would run it just once, alas, it's not yet that smart.

因此，MySQL将扫描学生的所有行，为每一行运行子查询，而不使用任何外部查询的任何索引。

So, MySQL will scan through all of the rows in students, running the subquery for each row, and not utilizing any indexes on the outer query whatsoever.

将查询写为JOIN将允许MySQL利用索引，并且以下查询将是写入它的最佳方式：

Writing the query as a JOIN would allow MySQL to utilize indexes, and the following query would be the optimum way to write it:

SELECT COUNT(*) AS count
FROMstudents s
JOIN classes c
  ON c.id = s.classes_id
  AND c.departments_id = 1
WHERE s.status = 1

这将使用以下索引：

students(`status`)
classes(`id`, `departements_id`) : multi-column index

这篇关于哪个MySQL查询更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！