Sample Header Ad - 728x90

MariaDB: Limit changes query plan and makes query very slow

0 votes
1 answer
409 views
It's counter-intuitive and weird to see how adding "LIMIT 2" changes whole plan for MariaDB (version 10.6.7) from using one index and having total time of 2 seconds into another index with total time 80 seconds.
SELECT SQL_NO_CACHE id FROM items WHERE .... ORDER BY updateTime ASC;
....
118 rows in set (2.531 sec)
vs.
SELECT SQL_NO_CACHE id FROM items WHERE .... ORDER BY updateTime ASC LIMIT 2;
...
2 rows in set (1 min 18.967 sec)
Explain shown next:
explain SELECT SQL_NO_CACHE id FROM items WHERE .... ORDER BY updateTime ASC;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: items
         type: ref
possible_keys: ....
          key: saleCompleted
      key_len: 1
          ref: const
         rows: 486318
        Extra: Using where; Using filesort
// works 2 seconds
vs.
explain SELECT SQL_NO_CACHE id FROM items WHERE .... ORDER BY updateTime ASC LIMIT 2;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: items
         type: index
possible_keys: ....
          key: updateTime
      key_len: 9
          ref: NULL
         rows: 62
        Extra: Using where
// works 80 seconds
I do understand how to change it (force using specific index OR change indexes), but I don't understand the logic behind the MariaDB. It seems like a bug, but I might be wrong. Also, it's so weird to see how filesort on 486k records works 40 times faster than other query plan (without filesorting on approx. 62 records). Are there some settings which can tweak MariaDB plan selection ? Thanks
Asked by Dmitriy Gorbenko (1 rep)
Oct 18, 2022, 02:38 PM
Last activity: Oct 18, 2022, 07:17 PM