When I execute the query below in MariaDB 10.1/MySQL 5.7 the results have 849 row and execute in 0,016 seconds.
SELECT a.*
FROM bco_dav_dm.d_ttlz_cli a
WHERE FL_ATND_FNAL = 0
AND a.ID_ATND = 218
ORDER BY A.FL_CRTR, A.DT_PRMR_ATVC_LNHA;
But when I add LIMIT clause to return only 1 row the query execute in 9 seconds!!! Why ?
I tested: Using LIMIT 1 the query execute in 9 seconds. Using LIMIT 2 the query execute in 9 seconds. Using LIMIT 3 the query execute in 9 seconds. Using LIMIT 4 and above (5,6,7,8...100, 200, 300, 400) the query execute in 0,016 seconds!!!
I've tested several times and I always have the same result.
How I will use this query in Web App, and I need only 1 record I don't know why LIMIT <=3 slow down the query!
In other posts, talks that using higher OFFSET naturally slow down the query, but I don't use OFFSET.
My Explain:
select_type: SIMPLE
table:a
type:ref
possible_keys:ID_ATND,FL_ATND_FNAL
key:ID_ATND
key_len:5
ref:const
rows:1846
Extra: Using where; Using filesort
EDITED:
I noticed when I use LIMIT 3 or below my explain change
select_type: SIMPLE
table:a
type:ref
possible_keys:ID_ATND,FL_ATND_FNAL
key:ORDER_BY_CRTR_DT
key_len:6
ref:const
rows:1764
Extra: Using where
The Index ORDER_BY_CRTR_DT is a composed Index that I use in my ORDER BY
INDEX ORDER_BY_CRTR_DT(FL_CRTR, DT_PRMR_ATVC_LNHA);
The cost based optimizer views the situation a bit different with different limits and just gets is plain wrong in this case. You will see this kind of weird behavior more often and in pretty much all cost based databases.
The place in this case where you see the difference is in the chosen index ORDER_BY_CRTR_DT and ID_ATND in the other plan, which the database then uses to estimate the number of rows. Seeing a lower number of rows, the cost based optimizer assumes the query is faster (simplified view point).
What sometimes can help is rebuilding the table and the indexes so that they all have the most recent information about the data in the histograms describing the data. Over time the result might change again due to inserts, updates and deletes, with as result that the plan can degrade again. Stabilization of the plan is however often reached by having a regular rebuild.
Alternative you can force the index to be used, with as result that the cost based optimizer is disabled for this plan. This can however backfire in the same way the cost based optimizer fails you right now.
Second alternative is to drop the index which gives you the 9s result, which might be an option if it is not used or has little impact on other queries.