0

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

3
  • Do you think including the table and index definitions might be helpful?
    – mustaccio
    Oct 18, 2022 at 17:02
  • Are there some settings which can tweak MariaDB plan selection ? IGNORE INDEX
    – Akina
    Oct 18, 2022 at 17:40
  • Does the WHERE clause use all the elements of saleCompleted? If so maybe append updateTime to the saleCompleted index. (Opps Rick already said this).
    – danblack
    Oct 22, 2022 at 1:55

1 Answer 1

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.

6
  • But do you know if there is a way to keep the same logic of query executor with or without having the limit in the query ? Because having ORDER and LIMIT means you have to hit the index directly to do the limit (otherwise, you have to sort and then limit, while having index allows you to pick item from the index without sorting them, because index already sorted them). So, like I do understand why why plan changed, but I just feel that it makes wrong estimations or decisions. Oct 20, 2022 at 15:56
  • @DmitriyGorbenko - I can't give you a simple answer/ There are too many combinations of WHERE, GROUP BY, ORDER BY, and LIMIT, plus the indexes. I will be happy to explain individual queries. Here's one general statement: If no index can be used for all of the WHERE and GROUP BY (if present) and ORDER BY, then there will be a sort pass and there cannot be a short-circuit for LIMIT.
    – Rick James
    Oct 20, 2022 at 19:07
  • No-no, no worries, I'm familiar with indexes and the rest of the ideas how to speed up the execution. I'm just looking for ways to control the logic of MariaDB. Anyway, thanks ! Oct 26, 2022 at 0:49
  • If you know which way the query should be performed for this case, you can add an "index hint" (FORCE, IGNORE, etc) to override the Optimizer.
    – Rick James
    Oct 26, 2022 at 1:52
  • Yeah, but this is a tunning on SQL level, and I'm taking about some DB-specific variables and settings. Oct 27, 2022 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.