Re: Why do mysql use temporary and filesort here?
MySQL can use an index for ordering only when the ordering happens on the first table that is non-system (ie. more than 1 row). In your case ordering happens based on junior_writer, which is the second table.
MySQL uses this join order, because it would need a table scan on junior_writer otherwise. Under certain conditions (eg. junior_writer is very small, or you have a limit clause) it can still be faster to do the scan.
Try these queries:
SELECT w.writername, w.writerid
FROM junior_writer w STRAIGHT JOIN junior_buddy b
WHERE b.writerid = w.writerid AND b.buddyid = 1
ORDER BY w.writername ASC
LIMIT 20
SELECT w.writername, w.writerid
FROM junior_writer w STRAIGHT JOIN junior_buddy b
WHERE b.writerid = w.writerid AND b.buddyid = 1
ORDER BY w.writername ASC
The second one just lacks the limit clause. I expect the first one to be quite fast. The second one depends on the size of the junior_writer table.
They both depend on two indexes:
junior_writer(writername)
junior_buddy(writerid, buddyid) -- Order of this one might need to be reversed, depending on your data, though I expect this order to be the better one. buddyid might eventually be left out without much of a problem.
Subject
Views
Written By
Posted
3707
September 08, 2006 04:50AM
Re: Why do mysql use temporary and filesort here?
2388
September 08, 2006 06:36AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.