4 ms·
I once had a huge performance problem for a simple self-join that I expected would run quickly. I talked to our resident SQL expert (retired from IBM, now doin
by dekhn 8y ago
I once had a huge performance problem for a simple self-join that I expected would run quickly.
I talked to our resident SQL expert (retired from IBM, now doing a job as the lab administrative assistant). She suggested sorting the data before inserting it.
That sped up the query.
it still bother me that data order matters so much. But, I can't reproduce the problem in MySQL any more anyway.
- miahi 8y agoI guess you were missing an index, as data order should only matter when doing full scans.
- dekhn 8y agothis 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 agoMySQL 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 agoIt'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.