I've 2 tables in 1:1 relation (however it possibly can be single:D relation later on) the following:

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 have to do is choose articles sorted first by article_top.until DESC and after by article.inserted DESC (therefore the "top" content is on top and also the relaxation is sorted in the latest towards the earliest).

I actually do following query, that is slow (fast after i skip the article_top.until within 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

Can there be anything I'm able to do for optimize the query beside merging the 2 tables into single one (losing chance of 1:N relation)?

I believed about adding additional column towards the table article and taking advantage of triggers to update it. By doing this I possibly could add index towards the both posts and ordering ought to be faster.

Can there be every other way how you can optimize the query?

Thanks

Give a top_until column towards the article table and also have its value replicated from article_top table (by hand at place time or using trigger) and provide articles that aren't in article_top table zero "top_until" value. Then possess a multi-column index on top_until and inserted posts:

INDEX( top_until, inserted )

and query such as this:

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

This will provide the results immediately.