adplus-dvertising

MySQL not using index to sort on and select a value from an indexed column

Asked 2 days ago
Viewed 8 times

Index

MySQL> show index from wp_terms;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| wp_terms |          1 | name     |            1 | name        | A         |      716638 |      191 |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Query

MySQL> select name from wp_terms order by name limit 1;
+--------------------+
| name               |
+--------------------+
|  ****************  |
+--------------------+
1 row in set (0.83 sec)

Explain

MySQL> explain select name from wp_terms order by name limit 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | wp_terms | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 802726 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

Why isn't the 'name' index even a possible key? Forcing it had no effect.

Thank you in advance.

asked 2 days ago

Correct Answer

Thanks to @Dai for pointing out the issue. 'name' was an index prefix, not a full index. Creating a full index on name resulted in index being used for the query.

MySQL> alter table wp_terms add index(name);
MySQL> explain select name from wp_terms order by name limit 1;
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | wp_terms | NULL       | index | NULL          | name_2 | 802     | NULL |    1 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-------------+

Thank you!

answered 2 days ago