MySQL Forums
Forum List  »  Optimizer & Parser

Re: Why do mysql use temporary and filesort here?
Posted by: Björn Steinbrink
Date: September 08, 2006 06:36AM

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.

Options: Reply• Quote


Subject
Views
Written By
Posted
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.