Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 12.3.2
-
Related to performance
Description
When using SQL_BUFFER_RESULT on a select with ARIA tables, the database experiences inexplicably awful performance despite a perfectly valid index being available.
Wrapping the select inside another select triggers normal behavior.
In both cases, the database has chosen an index + temporary strategy
Works:
MariaDB [newsrpm]> SET STATEMENT max_statement_time=5 FOR SELECT SQL_BUFFER_RESULT * FROM (SELECT date FROM articles ORDER BY date DESC LIMIT 0,1) as f1; |
+---------------------+
|
| date |
|
+---------------------+
|
| 2026-07-11 18:02:05 | |
+---------------------+
|
1 row in set (0.000 sec) |
Fails:
MariaDB [newsrpm]> SET STATEMENT max_statement_time=5 FOR SELECT SQL_BUFFER_RESULT date FROM articles ORDER BY date DESC LIMIT 0,1; |
ERROR 1969 (70100): Query was interrupted: execution time limit 5.0 sec exceeded |
|
Explain:
MariaDB [newsrpm]> explain SELECT SQL_BUFFER_RESULT date FROM articles ORDER BY date DESC LIMIT 0,1 |
-> ;
|
+------+-------------+----------+-------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+----------+-------+---------------+------+---------+------+------+------------------------------+
|
| 1 | SIMPLE | articles | index | NULL | date | 29 | NULL | 1 | Using index; Using temporary | |
+------+-------------+----------+-------+---------------+------+---------+------+------+------------------------------+
|
1 row in set (0.000 sec) |
 |
MariaDB [newsrpm]> explain SELECT SQL_BUFFER_RESULT * FROM (SELECT date FROM articles ORDER BY date DESC LIMIT 0,1) as f1 |
-> ;
|
+------+-------------+------------+-------+---------------+------+---------+------+---------+-----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+---------------+------+---------+------+---------+-----------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary | |
| 2 | DERIVED | articles | index | NULL | date | 29 | NULL | 2525918 | Using index | |
+------+-------------+------------+-------+---------------+------+---------+------+---------+-----------------+
|