| Bug #110971 | Optimizer: reverse index scan doesn't affect the cost | ||
|---|---|---|---|
| Submitted: | 10 May 2023 3:21 | Modified: | 10 May 2023 7:50 |
| Reporter: | Ilya Kantor | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 8.0.33 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 May 2023 3:21]
Ilya Kantor
Explains:
mysql> explain analyze select id from users force index(idx_users_name_created_at)
-> where name='Ann' order by created_at desc limit 10;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=5257 rows=10) (actual time=0.0573..0.0623 rows=10 loops=1)
-> Covering index lookup on users using idx_users_name_created_at (name='Ann') (reverse) (cost=5257 rows=39744) (actual time=0.0566..0.0604 rows=10 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> explain analyze select id from users force index(idx_users_name_created_at_desc)
-> where name='Ann' order by created_at desc limit 10;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 10 row(s) (cost=5257 rows=10) (actual time=0.0435..0.0469 rows=10 loops=1)
-> Covering index lookup on users using idx_users_name_created_at_desc (name='Ann') (cost=5257 rows=39744) (actual time=0.043..0.0452 rows=10 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[10 May 2023 7:50]
MySQL Verification Team
Hello Ilya Kantor, Thank you for the report and test case. regards, Umesh
Description: In `SELECT id FROM users WHERE name=? ORDER BY created_at DESC`, desc index isn't favored over a regular one. They have the same cost. I guess, the index `(name, created_at DESC)` should be prefered to `(name, created_at)`? These queries have exactly the same cost: explain analyze select id from users force index(idx_users_name_created_at) where name='Ann' order by created_at desc limit 10; explain analyze select id from users force index(idx_users_name_created_at_desc) where name='Ann' order by created_at desc limit 10; How to repeat: DROP TABLE IF EXISTS users; CREATE TABLE users( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, salary MEDIUMINT UNSIGNED, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME ); DROP TABLE IF EXISTS names; CREATE TABLE names ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(100) not null ); INSERT INTO names (name) VALUES ('Emma'), ('Liam'), ('Noah'), ('Olivia'), ('William'), ('James'), ('Isabella'), ('Oliver'), ('Sophia'), ('Benjamin'), ('Elijah'), ('Charlotte'), ('Lucas'), ('Mia'), ('Mason'), ('Amelia'), ('Ethan'), ('Harper'), ('Logan'), ('Ava'), ('Alexander'), ('Abigail'), ('Zed'), ('Emily'), ('Michael'), ('Evelyn'), ('Daniel'), ('Madison'), ('Matthew'), ('Avery'), ('Henry'), ('Sofia'), ('Jackson'), ('Ella'), ('Samuel'), ('Scarlett'), ('David'), ('Victoria'), ('Joseph'), ('Aria'), ('Carter'), ('Grace'), ('Owen'), ('Chloe'), ('Wyatt'), ('Penelope'), ('John'), ('Riley'), ('Jack'), ('Lily'), ('Luke'), ('Aubrey'), ('Jayden'), ('Zoey'), ('Dylan'), ('Lila'), ('Levi'), ('Ellie'), ('Gabriel'), ('Aaliyah'), ('Nicholas'), ('Addison'), ('Isaac'), ('Natalie'), ('Lincoln'), ('Luna'), ('Pete'), ('Ann'), ('Joshua'), ('Brooklyn'), ('Andrew'), ('Hazel'), ('Julian'), ('Aurora'), ('Grayson'), ('Audrey'), ('Leah'), ('Bella'), ('Mateo'), ('Claire'), ('Ryan'), ('Stella'), ('Jaxon'), ('Paisley'), ('Leo'), ('Skylar'), ('Jonathan'), ('Samantha'), ('Charles'), ('Nora'), ('Adam'), ('Eleanor'), ('Thomas'), ('Caroline'), ('Xavier'), ('Nova'), ('Eli'), ('Genesis'), ('Pete'), ('Ann'); -- insert 1m users INSERT INTO users (name, salary, created_at, deleted_at) WITH RECURSIVE seq AS (SELECT 1 as n UNION ALL SELECT 1 + n FROM seq LIMIT 1000000) SELECT -- random name (SELECT name FROM names WHERE id=n%100+1), -- random salary up to 10000 rand()*10000, DATE_ADD('2020-01-01', INTERVAL n MINUTE), -- 99% null, 1% deleted_at = created_at + random within 30 days IF(rand()>0.01, null, DATE_ADD(DATE_ADD('2020-01-01', INTERVAL n MINUTE), INTERVAL rand()*30 DAY)) FROM seq; CREATE INDEX idx_users_name_created_at ON users(name, created_at); CREATE INDEX idx_users_name_created_at_desc ON users(name, created_at DESC); ANALYZE TABLE users; -- same cost(?) explain analyze select id from users force index(idx_users_name_created_at) where name='Ann' order by created_at desc limit 10; explain analyze select id from users force index(idx_users_name_created_at_desc) where name='Ann' order by created_at desc limit 10;