MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimiztion of LEFT JOIN and LIMIT
Posted by: Rick James
Date: October 06, 2010 09:11PM

SELECT  *
    FROM  
      ( SELECT  *
            FROM  tableA
            ORDER BY  a_id
            LIMIT  100
      ) AS a
    LEFT JOIN  tableB AS b ON b.a_id = a.a_id
    LEFT JOIN  tableC AS c ON c.c_id = b.c_id
    LEFT JOIN  tableD AS d ON d.b_id = b.b_id

SHOW CREATE TABLE -- there need to be indexes on the ids, even in the other tables.

How does this compare?
SELECT  *
    FROM  tableA AS a
    LEFT JOIN  tableB AS b ON b.a_id = a.a_id
    LEFT JOIN  tableC AS c ON c.c_id = b.c_id
    LEFT JOIN  tableD AS d ON d.b_id = b.b_id 
    ORDER BY  a_id
    LIMIT  100

And do you really need "LEFT"?

If there is more to the real statement (eg a WHERE clause), show it; it could make a _big_ difference.

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...; -- with the code tags, it will be more readable
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Options: ReplyQuote


Subject
Views
Written By
Posted
3606
October 05, 2010 01:33PM
Re: Optimiztion of LEFT JOIN and LIMIT
1958
October 06, 2010 09:11PM


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.