MySQL Forums
Forum List  »  General

Re: Order by multiple columns from different tables speedup
Posted by: Rick James
Date: May 30, 2014 11:11AM

SELECT  *
    FROM  data
    JOIN  rel1 USING (field1)
    JOIN  rel2 USING (field2)
    JOIN  rel3 USING (field3)
    ORDER BY  rel1.order_field,
              rel2.order_field,
              rel3.order_field
A filesort is required for that query.
Here's the only way that query will be performed (well almost the only way):

1. Get information from one of the tables (not necessarily `rel1`);
2. Nested Loop Join NLJ to get information from another table;
3. NLJ into the third table;
4. As the rows are gathered, they are written to a temp table -- this might be a MEMORY table, or it might be MyISAM;
5. Sort that temp table;
6. Deliver the rows. If there were a LIMIT, it cannot be applied until this step.

An ORDER BY that spans multiple tables virtually requires a flow similar to the above.

If you would like to show us the specific case, there may be some workaround involving other aspects of the schema.
SHOW CREATE TABLE
EXPLAIN SELECT ...

Options: ReplyQuote


Subject
Written By
Posted
Re: Order by multiple columns from different tables speedup
May 30, 2014 11:11AM


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.