这个查询在MySQL中使用索引吗?

I'm not sure if this query is using indexes or not. How do I tell?

我不确定这个查询是否使用索引。我怎么告诉?

mysql> EXPLAIN SELECT au.* FROM users au WHERE au.id IN(SELECT fa.from_user_id FROM approvals fa INNER JOIN personas pp ON fa.persona_id = pp.id WHERE fa.to_user_id=1 AND pp.is_foundation=1 GROUP BY fa.from_user_id) ORDER BY id DESC LIMIT 0, 9999999999;
+----+--------------------+-------+--------+-----------------------+------------+---------+--------------------+------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys         | key        | key_len | ref                | rows | Extra                                        |
+----+--------------------+-------+--------+-----------------------+------------+---------+--------------------+------+----------------------------------------------+
|  1 | PRIMARY            | au    | index  | NULL                  | PRIMARY    | 4       | NULL               | 2272 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | fa    | ref    | to_user_id,persona_id | to_user_id | 4       | const              |  396 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | pp    | eq_ref | PRIMARY               | PRIMARY    | 4       | kjdb.fa.persona_id |    1 | Using where                                  |
+----+--------------------+-------+--------+-----------------------+------------+---------+--------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

3 个解决方案

#1

The key column in the output indicates the index that MySQL is using.

输出中的键列指示MySQL正在使用的索引。

So yes, the query uses indexes.

是的，查询使用索引。

You can read a lot more about the output of EXPLAIN in the MySQL documentation for the version of MySQL you are running. For example, if you're running MySQL 5.1, read http://dev.mysql.com/doc/refman/5.1/en/explain-output.html.

您可以在正在运行的MySQL版本的MySQL文档中阅读更多关于EXPLAIN的输出。例如，如果您正在运行MySQL 5.1，请阅读http://dev.mysql.com/doc/refman/5.1/ explain-output.html。

#2

I'll be quick. Yes, for joins, but not to filter records. Please take a look on Extra field. it should be "using index" while it's using where which is indeed slower.

我要快。是的，用于连接，但不用于过滤记录。请看看外景。它应该是“使用索引”，而它使用的地方确实更慢。

Please consider to revise query to avoid subqueries.

请考虑修改查询以避免子查询。

#3

Answered by @Trott.

由@Trott回答。

Though I think you quite capable and able to formulate and try the alternative queries, still, one weird fact of MySQL (easily overseen): MySQL claims since long, that an EXISTS can be better optimized than a IN SELECT, as the sought element then is inside the subquery.

尽管我认为您很有能力并且能够制定和尝试替代查询，但是，MySQL有一个奇怪的事实(很容易被监视):MySQL声称，从很长一段时间以来，存在的an可以比IN SELECT更好地优化，因为当时所查找的元素在子查询中。

That would be in its simplest form:

这是最简单的形式:

EXPLAIN
  SELECT au.*
  FROM users au
  WHERE EXISTS
    (SELECT fa.from_user_id FROM approvals fa
    INNER JOIN personas pp ON fa.persona_id = pp.id
    WHERE fa.from_user_id = au.id
      AND fa.to_user_id=1 AND pp.is_foundation=1
    GROUP BY fa.from_user_id)
  ORDER BY id DESC
  LIMIT 0, 9999999999;

#1