Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
N/A
Description
MySQL's WL#1074 mentions a limitation:
when user has two indexes, one ASC and another DESC over the same column, optimizer won't necessary will be able to pick the right index
I suppose it applies to MariaDB as well, even though MDEV-13756 doesn't say so.
However, there might still be room for improvement. At least in some cases MySQL is able to pick up the DESC index out of two, while MariaDB isn't:
# Remove the include if you run the test on MySQL 8.0 |
--source include/have_innodb.inc
|
|
create table t (a int, key aa(a), key ad(a desc)) engine=InnoDB; |
insert into t values (1),(5),(2),(8),(4),(6),(7),(9),(3); |
|
explain select * from t order by a desc; |
flush status;
|
select * from t order by a desc; |
show status like 'Handler_read%'; |
|
# Cleanup
|
drop table t; |
MariaDB preview-10.8-MDEV-13756-desc-indexes 43444ff5 |
explain select * from t order by a desc; |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t index NULL aa 5 NULL 9 Using index |
|
Handler_read_first 0
|
Handler_read_key 0
|
Handler_read_last 1
|
Handler_read_next 0
|
Handler_read_prev 9
|
Handler_read_retry 0
|
Handler_read_rnd 0
|
Handler_read_rnd_deleted 0
|
Handler_read_rnd_next 0
|
MySQL 8.0.23 |
explain select * from t order by a desc; |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t NULL index NULL ad 5 NULL 10 100.00 Using index |
|
Handler_read_first 1
|
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
|
ANALYZE/statistics collection doesn't change the outcome.
Same happens on MariaDB with MyISAM/Aria (ASC index is chosen), but it's not comparable to MySQL because MySQL doesn't support DESC indexes on MyISAM tables.
Attachments
Issue Links
- is caused by
-
MDEV-13756 Implement descending index: KEY (a DESC, b ASC)
-
- Closed
-
- relates to
-
MDEV-30845 filesort still select when desc/asc index matches ORDER BY
-
- Open
-