从过去n小时内选择观看次数最多的帖子的最佳方法 [英] Best approach to select most viewed posts from last n hours

问题描述

我正在使用PHP和MYSQL（innodb引擎）。

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

正如MYSQL参考所说，选择一列的比较和另一列的排序不能使用我们的考虑索引。

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

我有一张名为新闻的表。

此表包含至少100万条记录，其中包含两个重要列： time_added 和 number_of_views 。

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

我需要从最后 n 小时中选择查看次数最多的记录。这样做的最佳指标是什么？或者是否可以非常快速地为具有数百万条记录的表运行此类查询？

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?

我已经为最后一天做了这个，这意味着我可以通过添加新列（ date_added ）选择去年最常查看的记录。但如果我决定从上周选择这些记录，我又遇到了麻烦。

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.

推荐答案

首先，编写查询：

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

最佳指数是（time_added，number_of_views）。实际上， number_of_views 将不会用于完整查询，但我会将其包含在其他可能的查询中。

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.

这篇关于从过去n小时内选择观看次数最多的帖子的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！