Re: Optimiztion of LEFT JOIN and LIMIT

[Rick James]

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 ]