Optimize MySQL query perform ORDER BY before JOIN

Question

I have the following MSQL query, that is very slow

SELECT a.id, a.date, a.Title, a.Text, a.Text2, a.Text3, 
       b.xxx, b.yyyy, 
       c.aaaa, c.bbbb
FROM table_a a 
JOIN table_b b on a.Id = b.Id 
JOIN table_c c on a.eeee = c.id 
ORDER BY a.date DESC
LIMIT 200

The table_a has more that 100.000 rows. I need the last 200 rows in reverse order. What MySQL does is to select all rows, made the JOIN of all rows, then reverse the full table and finally select 200 record. The full process takes about 30-40 seconds.

Question: since table_a is ordered by a.id (it is a progressive number) and also by date (new articles are always added in the last position), is it possible to rewrite the query so that the JOIN is performed only on the latest 200 records and not the full table ? In this way the query will be much faster!

In general, how can be optimized ??

Answer 1

You can try this, but i am not realy sure if LIMIT is allowed.

SELECT a.id, a.date, a.Title, a.Text, a.Text2, a.Text3, 
       b.xxx, b.yyyy, 
       c.aaaa, c.bbbb
FROM (
SELECT
 id, date, Title, Text, Text2, Text3
 FROM table_a
 ORDER BY date DESC
 LIMIT 200
) as a  
JOIN table_b b on a.Id = b.Id 
JOIN table_c c on a.eeee = c.id;

Answer 2

This may be even faster than Bernd's suggestion:

SELECT  a2.id, a2.date, a2.Title, a2.Text, a2.Text2, a2.Text3,
        b.xxx, b.yyyy,
        c.aaaa, c.bbbb
    FROM   (
        SELECT  id
            FROM  table_a
            ORDER BY  date DESC
            LIMIT  200 
           ) as a1
    JOIN  table_b b  ON a1.Id = b.Id
    JOIN  table_c c  ON a1.eeee = c.id
    JOIN  table_a a2 ON a1.id = a2.id
    ORDER BY a2.date;   -- (this was needed in Bernd's answer)

together with

table_a:  INDEX(date, id)

This is because the inner query can be run very efficiently in that index instead of bouncing between the index BTree and the data BTree. After that 200 rows to deal with for the rest.

Another issue: If table_b and table_a are 1:1, why are they separate tables?

Yet another issue: If table_b and table_c are used for filtering ("1:0") or expanding (1:many) this won't work (and nor will Bernd's). You may get less than or more than 200 rows.