ORDER BY column of joined table performance

Question

I have 2 tables in 1:1 relation (but it possibly could become a 1:N relation in the future) as follows:

CREATE TABLE article (
   article_id INT,
   inserted DATETIME
) ENGINE InnoDB;

CREATE TABLE article_top (
   article_top_id INT,
   article_id INT,
   until DATETIME
) ENGINE InnoDB;

What I need to do is select articles sorted first by article_top.until DESC and after by article.inserted DESC (so the "top" articles are on the top and the rest is sorted from the newest to the oldest).

I do following query, which is slow (fast when I skip the article_top.until in the ORDER BY clause):

SELECT * FROM article 
LEFT JOIN article_top
ON article.article_id = article_top.article_id 
ORDER BY article_top.until DESC, article.inserted DESC
LIMIT 20

Is there anything I can do for optimize the query beside merging the two tables into single one (losing possibility of 1:N relation)?

I was thinking about adding additional column to the table article and using triggers to update it. This way I could add index to the both columns and ordering should be faster.

Is there any other way how to optimize the query?

Thanks

Answer 1

Add a top_until column to the article table and have its value copied from article_top table (manually at insert time or using trigger) and give articles that are not in article_top table zero "top_until" value. Then have a multi-column index on top_until and inserted columns:

INDEX( top_until, inserted )

and query like this:

SELECT * FROM article 
   ORDER BY top_until DESC, inserted DESC
   LIMIT 20

This should give the results instantaneously.