ORDER BY column with a joined table

Question

I have 2 tables in a 1: 1 ratio (but in the future this could become a 1: N ratio) 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 then article.inserted DESC (so the top articles are on top and the rest are sorted from newest to oldest).

I execute the following query, which is slow (fast when I skip 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 to optimize the query when combining two tables into one (it will lose the probability of a 1: N relationship)?

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

Is there any other way to optimize the query?

thanks

Answer 1

Add the top_until column to the article table and copy its value from the article_top table (manually during insertion or using a trigger) and specify the articles that are not listed in the article_top table with a value of "top_until" zero. Then enter an index with multiple columns in the top_until and inserted columns:

 INDEX( top_until, inserted ) 

and execute the following queries:

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

This should give results instantly.