Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

On “order by” optimization

dom.as

Post reply on HN
Print view View original

11–16 of 16 posts

Re: On “order by” optimization

#11

Post by Animats »

Right. MySQL's optimizer knows how many records each table has, but not much else. It doesn't do statistical tests on the tables. Depending on table contents, "WHERE b=X" can select any number of records, from zero to the whole database. Whether the optimal strategy is to do the "WHERE b=X" first, possibly getting a large number of hits, or simply making a sequential pass, is not decidable from the database size only.

An alternative would be for the optimizer to do an indexed SELECT on b for "WHERE b=X", and if the number of hits exceeded some threshold, abandon that effort as inefficient and start over using a sequential pass. But MySQL doesn't abandon strategies once selected, or keep such statistics as a hint for future queries. As someone pointed out, Oracle's flagship database product does do that. Anyone know about Postgres?

Re: On “order by” optimization

#12

Post by acqq »

crishoj wrote: post #9
goldenkey wrote: post #4

Earlier quoted context omitted.

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...

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.

Re: On “order by” optimization

#13

Post by crishoj »

acqq wrote: post #12
crishoj wrote: post #9

Earlier quoted context omitted.

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.

I see. Thanks.

Re: On “order by” optimization

#14

Post by midom »

schrodinger wrote: post #8
goldenkey wrote: post #3

Subselects 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!

So sad I can't see or reply to that comment anymore - I'm the author. Sorry, HN, my personal blog rarely gets that wide circulation, I should've done better job at introducing the context.

This was just one of many systems that thousands of facebook engineers build and they did not think of some edge cases. We do know how to write queries or to operate our databases, in general.

Re: On “order by” optimization

#15

Post by midom »

acqq wrote: post #2

In 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…

LIMIT 2 is also exposed to this issue, at different data ratios (in my tests up to LIMIT 142, I went through the math).

Re: On “order by” optimization

#16

Post by acqq »

midom wrote: post #15
acqq wrote: post #2

In 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…

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.
Post reply on HN