Description:
Record buffer sizing for the index scan below ignores LIMIT 1. Although the query needs only the first matching row, the storage engine fills a buffer with many rows before returning that row.
This is related to Bug#119541:
https://bugs.mysql.com/bug.php?id=119541
The patch contributed there restores a 100-row cap and substantially improves performance, but this query still reads 99 unnecessary rows. LIMIT-aware sizing should avoid using a record buffer altogether for this query, while preserving larger buffers for scans that benefit from them.
How to repeat:
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a INT,
b INT,
KEY i1 (b, a)
);
SET SESSION cte_max_recursion_depth = 20000;
INSERT INTO t1(a, b)
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 16384
)
SELECT 1, 1 FROM seq;
ANALYZE TABLE t1;
SELECT b, a FROM t1 FORCE KEY(i1)
WHERE b = 1 ORDER BY a DESC LIMIT 1;
Actual result:
Buffer sizing uses the scan's estimated cardinality without accounting for LIMIT. In measurements using the equivalent reproducer from Bug#119541, unmodified trunk allocated a buffer for 1596 rows. With the contributed cap restored, it allocated space for 100 rows.
Expected result:
Recognize that only one row needs to be read and do not allocate a record buffer.
Suggested fix:
Account for LIMIT when estimating how many rows an access needs to read. For queries with filtering or joins, account for the additional input rows needed to produce the requested number of result rows. Preserve existing buffer sizes for scans without LIMIT.
© 2026, Oracle Corporation and/or its affiliates