从过去n小时中选择观看次数最多的帖子的最佳方法

Question

I'm using PHP and MYSQL(innodb engine).

As MYSQL reference says, selecting with comparison of one column and ordering by another can't use our considered index.

I have a table named News.

This table has at least 1 million records with two important columns: time_added and number_of_views.

I need to select most viewed records from last n hours. What is the best index to do this? Or is it possible to run this kind of queries very fast for a table with millions of records?

I've already done this for "last day", meaning I can select most viewed records from last day by adding a new column (date_added). But if I decide to select these records from last week, I'm in trouble again.

Answer 1

First, write the query:

select n.*
from news n
where time_added >= date_sub(now(), interval <n> hours)
order by number_of_views desc
limit ??;

The best index is (time_added, number_of_views). Actually, number_of_views won't be used for the full query, but I would include it for other possible queries.