Reproduction for 5.7.35. # Initialise a sandbox. dbdeployer deploy single mysql_5.7.35 # Load a dump similar to what is in prod (without indexes for fast load). # Create indexes and add auto-increment. alter table p add index(id), modify column id bigint unsigned NOT NULL AUTO_INCREMENT, add index(customer_id, id); # Table size. -rw-r----- 1 xxx xxx 15G Jul 26 19:09 msb_mysql_5_7_35/data/test_jfg/p.ibd # Show the table structure. mysql [localhost:5735] {msandbox} (test_jfg) > show create table p\G *************************** 1. row *************************** Table: p Create Table: CREATE TABLE `p` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `customer_id` int(10) unsigned NOT NULL, `object_id` bigint(20) unsigned NOT NULL, `type_id` tinyint(3) unsigned NOT NULL, `details` text, PRIMARY KEY (`customer_id`,`object_id`,`id`), KEY `id` (`id`), KEY `customer_id` (`customer_id`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=344801716 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) # The plan for the query is using the wrong index. mysql [localhost:5735] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: index possible_keys: PRIMARY,customer_id key: id key_len: 8 ref: NULL rows: 753 filtered: 1.33 Extra: Using where 1 row in set, 1 warning (0.00 sec) # And the query is horribly slow (interrupted after 10 seconds and scanned a lot of rows). mysql [localhost:5735] {msandbox} (test_jfg) > pager cat > /dev/null; set max_execution_time = 10000; flush local status; SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10; pager; show status like "Handler_read%"; PAGER set to 'cat > /dev/null' Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded Default pager wasn't set, using stdout. +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 1228658 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+---------+ 7 rows in set (0.00 sec) # The good plan, hinting an index, has a wrong estimate for rows(I would expect 10). mysql [localhost:5735] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p use index (customer_id) WHERE customer_id = @cid ORDER BY id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: ref possible_keys: customer_id key: customer_id key_len: 4 ref: const rows: 1281340 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) # And executing the good plan, hinting an index, is fast and only examining 10 rows (as expected). mysql [localhost:5735] {msandbox} (test_jfg) > pager cat > /dev/null; set max_execution_time = 10000; flush local status; SELECT id, object_id, details FROM p use index (customer_id) WHERE customer_id = @cid ORDER BY id LIMIT 10\G pager; show status like "Handler_read%"; PAGER set to 'cat > /dev/null' Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) 10 rows in set (0.00 sec) Default pager wasn't set, using stdout. +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 9 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec) # I would expect the good plan to have rows like this. mysql [localhost:5735] {msandbox} (test_jfg) > explain SELECT id FROM p ORDER BY id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: index possible_keys: NULL key: id key_len: 8 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) # When I add WHERE id > @id to the query with a good estimate, the estimate becomes wrong even though the query is fast. mysql [localhost:5735] {msandbox} (test_jfg) > explain SELECT id FROM p WHERE id > @id ORDER BY id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: range possible_keys: id key: id key_len: 8 ref: NULL rows: 52148625 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) mysql [localhost:5735] {msandbox} (test_jfg) > pager cat > /dev/null; flush local status; SELECT id FROM p WHERE id > @id ORDER BY id LIMIT 10; pager; show status like "Handler_read%"; PAGER set to 'cat > /dev/null' Query OK, 0 rows affected (0.00 sec) 10 rows in set (0.00 sec) Default pager wasn't set, using stdout. +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 9 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.01 sec) # Showing it has something to do with the compound PK with still a bad estimate even with the good plan. mysql [localhost:5735] {msandbox} (test_jfg) > alter table p drop primary key, add primary key (id), drop index id, drop index customer_id, add index(customer_id); Query OK, 0 rows affected (21 min 47.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:5735] {msandbox} (test_jfg) > show create table p\G *************************** 1. row *************************** Table: p Create Table: CREATE TABLE `p` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `customer_id` int(10) unsigned NOT NULL, `object_id` bigint(20) unsigned NOT NULL, `type_id` tinyint(3) unsigned NOT NULL, `details` text, PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`) ) ENGINE=InnoDB AUTO_INCREMENT=344801716 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql [localhost:5735] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: ref possible_keys: customer_id key: customer_id key_len: 4 ref: const rows: 1366822 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) mysql [localhost:5735] {msandbox} (test_jfg) > pager cat > /dev/null; set max_execution_time = 10000; flush local status; SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10; pager; show status like "Handler_read%"; PAGER set to 'cat > /dev/null' Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) 10 rows in set (0.00 sec) Default pager wasn't set, using stdout. +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 9 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec)