Details
Major Description
Hi, MariaDB developers, thanks for reading this report. I found a bug in the estimator.
When executing EXPLAIN SELECT * FROM t0 LIMIT 1 on a table with a primary key index, the optimizer reports an estimated row count of 999000 (almost the full table size), even though the query only needs to return one row. In contrast, the same query with an explicit ORDER BY t0.c0 ASC LIMIT 1 produces a correct estimate (rows=1).
This indicates that the optimizer fails to recognize that a LIMIT 1 without an ORDER BY can stop scanning after fetching the first physical row (which is essentially the first record in the primary key order due to clustered index storage). The overestimation may lead to suboptimal query plans in more complex scenarios.
CREATE TABLE t0(c0 INT PRIMARY KEY); |
INSERT INTO t0 SELECT seq FROM seq_1_to_1000000; |
|
|
EXPLAIN SELECT * FROM t0 LIMIT 1; |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+ |
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 999000 | | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+ |
|
|
EXPLAIN SELECT * FROM t0 ORDER BY t0.c0 ASC LIMIT 1; |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+ |
| 1 | SIMPLE | t0 | index | NULL | PRIMARY | 4 | NULL | 1 | | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------+ |