On “order by” optimization
1–10 of 16 posts
Re: On “order by” optimization
#2In short, if I understood, the author used
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.
Re: On “order by” optimization
#3Subselects are almost always better when it comes to optimization.
The author is really amateur. His inability to do what amounts to SQL I in college is quite telling, and somehow hes opinionated enough to write a huge blog article about it. I like the analysis and diagnostics of the query planning - but his query writing is trashgutter.
SELECT c,d FROM t WHERE a=(SELECT MAX(a) WHERE b=X from t)
Re: On “order by” optimization
#4In short, if I understood, the author used 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 serio…
Its usually better to be explicit and use aggregates like MAX or MIN instead of a LIMIT of 1.
Re: On “order by” optimization
#5Subselects are almost always better when it comes to optimization. The author is really amateur. His inability to do what amounts to SQL I in college is quite telling, and somehow hes opinionated enough to write a huge blog article about it. I like the analysis and diagnostics of the query planning - but his query writing is trashgutter. SELECT c,d FROM t WHERE a=(SELECT MAX(a) WHERE b=X from t) That query is what I…
It sounds like you have a lot to teach us about query writing, but gratuitously putting someone else down, even when they don't know as much as you do, is not ok here. It violates both important rules of HN comments: civility (it truly is gratuitously negative) and substance, because it impedes the absorption of real information in your comment.
In the future, please edit that stuff out and stick to the substance when commenting here.
Re: On “order by” optimization
#6Great read!
Re: On “order by” optimization
#7In short, if I understood, the author used 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 serio…
Its usually better to be explicit and use aggregates like MAX or MIN instead of a LIMIT of 1.
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!
Re: On “order by” optimization
#8Subselects are almost always better when it comes to optimization. The author is really amateur. His inability to do what amounts to SQL I in college is quite telling, and somehow hes opinionated enough to write a huge blog article about it. I like the analysis and diagnostics of the query planning - but his query writing is trashgutter. SELECT c,d FROM t WHERE a=(SELECT MAX(a) WHERE b=X from t) That query is what I…
Yea, the guy that goes into the source code of the optimizer to figure out the cause for a poorly optimized query plan is the amateur!
Re: On “order by” optimization
#9In short, if I understood, the author used 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 serio…
Its usually better to be explicit and use aggregates like MAX or MIN instead of a LIMIT of 1.
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...
Re: On “order by” optimization
#10>>Unfortunately, optimizer doesn’t understand, that there’re humans who are building these systems, and humans have their own rationale and thinking. One of the ideas that a human would have is that if you have 100GB table, you better understand things like data locality and other sorts of efficiencies.
I have not used MySql and cannot comment for that but with my experience with Oracle, i believe its histogram generation process is actually trying to solve the same problem.It has a binning strategy for histogram generation that tries to help with data patterns.