Performance drop upgrading from MySQL 5.7.33 to 8.0.31 - why did it stop using the Index Condition Pushdown Optimization?

I have a table like this (details elided for readability):

CREATE TABLE UserData (
  id bigint NOT NULL AUTO_INCREMENT,
  userId bigint NOT NULL DEFAULT '0', ...
  c6 int NOT NULL DEFAULT '0', ...
  hidden int NOT NULL DEFAULT '0', ...
  c22 int NOT NULL DEFAULT '0', ...
  PRIMARY KEY (id), ...
  KEY userId_hidden_c6_c22_idx (userId,hidden,c6,c22), ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 

and was happily doing queries on it like this in MySQL 5.7:

mysql> select * from UserData use index (userId_hidden_c6_c22_idx) where (userId = 123 AND hidden = 0) order by id DESC limit 10 offset 0;
+----
| ...
+----
10 rows in set (0.03 sec)

However, in MySQL 8.0 these queries started doing this:

mysql> select * from UserData use index (userId_hidden_c6_c22_idx) where (userId = 123 AND hidden = 0) order by id DESC limit 10 offset 0;
+----
| ...
+----
10 rows in set (1.56 sec)

Explain shows the following, 5.7:

mysql> explain select * from UserData use index (userId_hidden_c6_c22_idx) where (userId = 123 AND hidden = 0) order by id DESC limit 10 offset 0;
+----+-------------+----------+------------+------+---------------+---------------+---------+-------------+-------+----------+---------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key           | key_len | ref         | rows  | filtered | Extra                                 |
+----+-------------+----------+------------+------+---------------+---------------+---------+-------------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | UserData | NULL       | ref  | userId_hidden_c6_c22_idx | userId_hidden_c6_c22_idx | 12      | const,const | 78062 |   100.00 | Using index condition; Using filesort |
+----+-------------+----------+------------+------+---------------+---------------+---------+-------------+-------+----------+---------------------------------------+

8.0:

mysql> explain select * from UserData use index (userId_hidden_c6_c22_idx) where (userId = 123 AND hidden = 0) order by id DESC limit 10 offset 0;
+----+-------------+----------+------------+------+---------------+---------------+---------+-------------+-------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key           | key_len | ref         | rows  | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+---------------+---------+-------------+-------+----------+----------------+
|  1 | SIMPLE      | UserData | NULL       | ref  | userId_hidden_c6_c22_idx | userId_hidden_c6_c22_idx | 12      | const,const | 79298 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+---------------+---------+-------------+-------+----------+----------------+

The main difference seems to be that 5.7 is Using index condition; Using filesort and 8.0 is only Using filesort.

<s>Why did 8.0 stop using the index condition, and how can I get it to start using it?</s>

EDIT: Why did performance drop 10-100x with MySQL 8.0? It looks like it's because it stopped using the Index Condition Pushdown Optimization - how can I get it to start using it?

The table has ~150M rows in it, and that user has ~75k records, so I guess it could be a change in the size-based heuristics or whatever goes into the MySQL decision making?

In the EXPLAIN you show, the type column is ref and the key column names the index, which indicates it is using that index to optimize the lookup.

You are making an incorrect interpretation of what "index condition" means in the extra column. Admittedly it does sound like "using the index" versus not using the index if that note is absent.

The note about "index condition" is referring to Index Condition Pushdown, which is not related to using the index, but it's about delegating other conditions to be filtered at the storage engine level. Read about it here: https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

It's unfortunate that the notes reported by EXPLAIN are so difficult to understand. You really have to study a lot of documentation to understand how to read those notes.

This would be much faster in either version because it would stop after 10 rows. That is, the "filesort" would be avoided.

INDEX(userId, hidden, id)

This won't do "Using index" (aka "covering"), but neither did your attempts. That is different from "Using index condition" (aka "ICP", as you point out).

Try these to get more insight:

EXPLAIN FORMAT_JSON SELECT ...
EXPLAIN ANALYZE SELECT ...

(No, I cannot explain the regression.)

The question should really be: 'Why did it use ICP in 5.7'?

The only index condition which could have been 'pushed' to the index has to be extracted from the condition 'userId = 123 AND hidden = 0'.

That condition is already completely evaluate by using the 'ref' access type on userId_hidden_c6_c22_idx, with the 'ref' being "const,const". (Refering to that the 'const' literals [123,0] being used.

Thus, adding the condition 'userId = 123 AND hidden = 0' on the index does not improve the selectivity.

The optimizer in 8.0 has been improved to detect this redundanzy, thus does not add it as an ICP.