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?
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
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.