3 ms·

I guess you were missing an index, as data order should only matter when doing full scans.

• dekhn 8y ago
  this was a full self-join (SELECT * FROM blah a, JOIN blah b WHERE a.id = b.id). I ran strace on it, for each a item, it was doing a seek to each b row and reading a whole page. By pre-sorting, I increased locality.
  • adrianmsmith 8y ago
    MySQL only supported “nested loop” joins in 5.5 and below. Hash and merge joins were introduced in 5.6. Depending on when you originally did the query, maybe you were using 5.5 or below, which would explain why you now can’t reprodce it?
    • dekhn 8y ago
      It's very possible. I was running the query ~14 years ago and it wouldn't surprise me if any number of improved condition pushdown mechanisms were implemented.