LIMIT 2 is also exposed to this issue, at different data ratios (in my tests up to LIMIT 142, I went through the math).
Can you please explain: Does MySQL use index over (b,c) or not where evaluating "where b=X" or not? As I've said, I'd expect any engine which has an index to use it.
Its usually better to be explicit and use aggregates like MAX or MIN instead of a LIMIT of 1.
Re:On “order by” optimization
by degenerate on Friday July 31, 2015 @10:38PM
(#9985657)
Can you elaborate on that with an example? I can't imagine when running MAX() or MIN() could be better than a static digit, so an example would help me understand your comment!
As in SELECT MAX(a), c, d FROM t WHERE b=x?
MySQL will return a random c and d, not necessarily the c and d from the row with maximum a...
Goldenkey actually posted
SELECT c,d FROM t WHERE a=(SELECT MAX(a) WHERE b=X from t)
But that post got flagged because he also gave his opinion on the level of understanding the article writer has. The query you posted is actually the one by the article writer.
SELECT c,d FROM t WHERE b=X ORDER BY a DESC LIMIT 1
on MySQL and was surprised to find that MySQL was using index over a and in his opinion fully ignored the existence of the index over (b,c) to discover where b is equal to X? I admit I didn't understand his explanation why, especially not why it would maybe do the right thing with the LIMIT 2. I can't imagine that any serious SQL engine would ignore the possibility to use the index in such a case.