Description:
8.0 optimizer don't choose ICP when using a condition on secondary index, along with a ORDER BY.. LIMIT.
However, 5.7.36 seems just fine with ICP.
I saw the 5.7 version source code, and locate that 5.7 will call 'add_ref_to_table_cond(thd,tab)' to create the condition to be pushed down.
How to repeat:
CREATE TABLE `f1` (
`table_index` int(11) NOT NULL AUTO_INCREMENT,
`id` int(11) NOT NULL,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`table_index`,`id`),
KEY `idx_id` (`id`),
KEY `idx_1` (`number`)
);
MySQL8.0.22:
mysql> explain select * from f1 where id=1 order by number desc limit 1,1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | f1 | NULL | ref | idx_id,idx_1 | idx_id | 4 | const | 1 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------+
MySQL 5.7.36:
mysql> explain select * from f1 where id=1 order by number desc limit 1,1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | f1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+
Suggested fix:
As noted in MySQL 5.7.36:
/*
Because filesort always does a full table scan or a quick range scan
we must add the removed reference to the select for the table.
We only need to do this when we have a simple_order or simple_group
as in other cases the join is done before the sort.
*/
Did MySQL 8.0 do something like this, or avoid this situation?
Description: 8.0 optimizer don't choose ICP when using a condition on secondary index, along with a ORDER BY.. LIMIT. However, 5.7.36 seems just fine with ICP. I saw the 5.7 version source code, and locate that 5.7 will call 'add_ref_to_table_cond(thd,tab)' to create the condition to be pushed down. How to repeat: CREATE TABLE `f1` ( `table_index` int(11) NOT NULL AUTO_INCREMENT, `id` int(11) NOT NULL, `number` int(11) DEFAULT NULL, PRIMARY KEY (`table_index`,`id`), KEY `idx_id` (`id`), KEY `idx_1` (`number`) ); MySQL8.0.22: mysql> explain select * from f1 where id=1 order by number desc limit 1,1; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------+ | 1 | SIMPLE | f1 | NULL | ref | idx_id,idx_1 | idx_id | 4 | const | 1 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------+ MySQL 5.7.36: mysql> explain select * from f1 where id=1 order by number desc limit 1,1; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+ | 1 | SIMPLE | f1 | NULL | ref | idx_id | idx_id | 4 | const | 1 | 100.00 | Using index condition; Using filesort | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+ Suggested fix: As noted in MySQL 5.7.36: /* Because filesort always does a full table scan or a quick range scan we must add the removed reference to the select for the table. We only need to do this when we have a simple_order or simple_group as in other cases the join is done before the sort. */ Did MySQL 8.0 do something like this, or avoid this situation?