Bug #88127 Index not used for 'order by' query with utf8mb4 character set
Description:
A simple query on an indexed column encoded with the utf8mb4 character set with an "order by" clause, such as the query below, unexpectedly results in a filesort:
mysql> explain select * from test force index (B) where B = 'foo' order by B asc;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
| 1 | SIMPLE | test | NULL | ref | B | B | 82 | const | 1 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
This is only observed when the connection character set is also utf8mb4. If the connection character set is set to utf8 then a sort isn't performed. Forcing the index doesn't change the plan.
I would expect that a filesort would not be required when the character sets of the index and connection are the same.
Query optimizer trace showing index_provides_order is false is attached.
How to repeat:
Step 1: Create the table
CREATE TABLE `test` (
`A` varchar(10) COLLATE utf8mb4_bin NOT NULL,
`B` varchar(20) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`A`),
KEY `B` (`B`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Step 2: Query with character_set_connection set to 'utf8mb4' and see filesort being used;
mysql> show variables like 'character\_set\_%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+---------+
7 rows in set (0.00 sec)
mysql> explain select * from test where B = 'foo' order by B asc;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
| 1 | SIMPLE | test | NULL | ref | B | B | 82 | const | 1 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Forcing the index doesn't help either:
mysql> explain select * from test force index (B) where B = 'foo' order by B asc;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
| 1 | SIMPLE | test | NULL | ref | B | B | 82 | const | 1 | 100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Step 3: Query with character_set_connection set to 'utf8' and see that filesort isn't used:
mysql> set character_set_connection='utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from test where (B = 'foo') order by B asc;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | B | B | 82 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.