MariaDB: Limit changes query plan and makes query very slow

Question

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

Answer 1

"Damned if you do, damned if you don't."

When the LIMIT is really useful, the query can run a lot faster. But the Optimizer does not have the statistics necessary to figure that out.

So, yes, it guessed "wrong".

Try it with INDEX(saleCompleted, updateTime). And you should probably get rid of both INDEX(saleCompleted) and INDEX(updateTime), both of which you seem to have.

Depending on what else is in the WHERE, that index may run faster than either query, and do so in all situations.

Let's see the full query and SHOW CREATE TABLE to see if some different advice is warranted.