Details
Major Description
Hi, thanks for reading my report. I find a missed optimization.
Step to reproduce:
CREATE TABLE t0 (c0 INT NOT NULL, part INT NOT NULL, PRIMARY KEY (c0)); |
INSERT INTO t0 SELECT seq, seq FROM seq_1_to_1000000; |
|
|
-- (A) expected: PRIMARY index reverse scan, stop after 1 row
|
ANALYZE FORMAT=JSON SELECT c0 FROM t0 ORDER BY c0 DESC LIMIT 1 |
/*
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_total_time_ms": 0.023515057,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t0",
|
"access_type": "index",
|
"key": "PRIMARY",
|
"used_key_parts": ["c0"],
|
"rows": 998739,
|
"r_rows": 1,
|
"r_table_time_ms": 0.014215601,
|
"r_engine_stats": { "pages_accessed": 3 }
|
}
|
}
|
]
|
}
|
}
|
*/
|
|
|
-- (B) redundant ORDER BY part — should match (A)
|
ANALYZE FORMAT=JSON SELECT c0 FROM t0 ORDER BY c0 DESC, part DESC LIMIT 1 |
/*
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_total_time_ms": 116.5682576,
|
"nested_loop": [
|
{
|
"read_sorted_file": {
|
"r_rows": 1,
|
"filesort": {
|
"sort_key": "t0.c0 desc, t0.part desc",
|
"r_total_time_ms": 116.5325276,
|
"r_limit": 1,
|
"r_used_priority_queue": true,
|
"table": {
|
"table_name": "t0",
|
"access_type": "ALL",
|
"rows": 998739,
|
"r_rows": 1000000,
|
"r_table_time_ms": 73.01364779,
|
"r_engine_stats": { "pages_accessed": 1748 }
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
*/ |
Because `c0` is the primary key, `ORDER BY c0 DESC, part DESC LIMIT 1` must return the same row as `ORDER BY c0 DESC LIMIT 1`, so MariaDB should still walk the primary index in reverse and stop after one row; instead, the extra `ORDER BY` column disables the ordered index plan and forces a full table scan plus filesort over all 1e6 rows, turning a ~0.03 ms index lookup into a ~117 ms sort.