0

Warm run. A has millions of records, B,C,D... are smaller (thousands)

SELECT ... FROM A LEFT OUTER JOIN B ... LIMIT 1000 OFFSET 0;

this returned in < 0.1 seconds

SELECT ... FROM A LEFT OUTER JOIN B ... LIMIT 1000 OFFSET 1000000;

while this returns in > 4 seconds

Assuming the ordering is by A's PK, and all the joins are correctly indexed, I would have assumed the 2 operations ought to have similar performance, but it seems like its O(N) according to the offset...

Shouldn't the query plan be to select the 1000 out of A first and then do the join? I compared a select of just A with the two different offsets, and the time difference is much less than 1 second, so that shouldn't explain the large difference in the time given the joins.

Is this a deficiency in mysql query plan optimizer?

Xerion
  • 3,141
  • 5
  • 30
  • 46
  • [`This question`](http://stackoverflow.com/questions/1243952/how-can-i-speed-up-a-mysql-query-with-a-large-offset-in-the-limit-clause) seems to be somewhat similar. Perhaps it has useful information for you. – Mark Wilkins Jan 26 '12 at 19:04

1 Answers1

1

Since you don't mention a ORDER BY clause the query can be stopped after the 1,000 records stated in the limit in the first query. The second query is slower because 1,001,000 records have to be generated and then only the last 1,000 will be returned.

Hope that helps.

dgw
  • 13,418
  • 11
  • 56
  • 54
  • actually, w/o explicit order by, i believe mysql will just use the PK for ordering. I did try an order by PK clause in my initial test, and there was no affect on timing. – Xerion Jan 26 '12 at 19:03
  • And did you try to order by another column (not PK)? – dgw Jan 26 '12 at 20:06