mysql - 一个非常简单的不同查询使用临时表

Question

出于某种原因，这个非常简单的 WordPress 查询使用了临时表。我什至尝试过强制它使用索引，但无济于事。

EXPLAIN SELECT DISTINCT(meta_key) FROM wp_postmeta

+------+-------------+-------------+------+---------------+------+---------+------+---------+-----------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra           |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-----------------+
|    1 | SIMPLE      | wp_postmeta | ALL  | NULL          | NULL | NULL    | NULL | 1756487 | Using temporary |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-----------------+


SHOW INDEX FROM wp_postmeta

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_postmeta |          0 | PRIMARY  |            1 | meta_id     | A         |     1756487 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | post_id  |            1 | post_id     | A         |      159680 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | meta_key |            1 | meta_key    | A         |        1578 |      191 | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

如果您尝试使用 GROUP BY 而不是 SELECT DISTINCT 会怎样？

SELECT meta_key
FROM wp_postmeta
GROUP BY meta_key