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

Question

The Table

https://codex.wordpress.org/Database_Description

CREATE TABLE wp_terms (
    term_id    bigint(20) unsigned auto_increment,
    name       varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
    slug       varchar(200),
    term_group bigint(10) DEFAULT 0,

    PRIMARY KEY ( term_id )
) ENGINE=InnoDB;

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

The Query

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

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

The Question

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

Thank you in advance.

What is the exact column-type specification of wp_terms.name? (including NULL / NOT NULL) — Dai
– Dai, Commented Sep 23, 2022 at 2:37
What version of MySQL are you using? And what storage-engine are you using for the wp_terms table? — Dai
– Dai, Commented Sep 23, 2022 at 2:38
I have no explanation: Large table, index on name, with high cardinality should mean the index is being used. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 23, 2022 at 2:39
@TimBiegeleisen I noticed the index's sub_part is 191 (and not NULL), which means the column is only partially indexed, could that be why? — Dai
– Dai, Commented Sep 23, 2022 at 2:42

Alex C · Accepted Answer · 2022-09-23 02:50:35Z

2

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 Sep 23, 2022 at 2:50

Alex C

615 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Dai Over a year ago

How did you create the prefix-index in the first place?

Alex C Over a year ago

@Dai I inherited the DB... just working on optimizing it.

Dai Over a year ago

Working on a heavily-customized WordPress install? You have my sympathy.

Alex C Over a year ago

Haha, thanks! Getting it done though. Amazing what can be achieved with a few well targeted indexes! This one had me stumped though. TIL about index prefixes!

Dai Over a year ago

I avoid MySQL as much as I can, so pardon my ignorance, but just what is an index-prefix? (I honestly don't know, I used this QA as an opportunity to read-up on what EXPLAIN reports, my (upvoted) comments were from just reading the docs) - I don't think I've ever come across anything similar in any other RDBMS.

|

Collectives™ on Stack Overflow

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

The Table

The Index

The Query

The `Explain`

The Question

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The Table

The Index

The Query

The Explain

The Question

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

The `Explain`