什么是数据排序/排序的正确和最有效的方法 [英] What is the correct and most efficient approach for data ranking/ordering

问题描述

我有一个自定义文章类型，有数以千计的帖子。这些帖子中的每一个都具有称为分数（其是数值）的元数据。我想根据得分值对所有这些帖子排名。因此post_id和score是我正在寻找的两个值。帖子的排名位置将在我的网站的各个地方使用。

现在，什么是正确和最有效的方式这样做？我应该：

1. 只需获取所有post_ids和分数，并将它们存储在数组中。




2. 创建一个单独的表，填充它的数量， post_id和分数集，然后对表进行排序。




3. 创建一个单独的表，填入post_id和分数集，并对表进行排序。

我需要的只是在每个帖子中具有得分显示，并且还具有使用排名数据的排名小部件。所以，如果数据总是最新的（后得分调整 - >排名更新）将是巨大的。但是如果这将在数据库/服务器上创建一个沉重的负载，那么如果数据不是最新的，但是X分钟以前也是如此。

由于我对数据库很新，我很难知道什么负载不同的方法将创建。

解决方案

因为排名不是静态过程，所以需要更新每次有一个插入或更新到你表
我建议你在查询中处理如下：

  SELECT @rownum：= @ rownum + 1 as row_number，
 t。* 
 FROM（
< your ordered query here here> 
）t，
 SELECT @rownum：= 0）r 
  

I am trying to wrap my head around a ranking feature for wordpress posts.

I have a Custom Post Type with thousands of posts. Each one of these posts has meta data called score (which is numerical). I want to have a ranking for all these posts based on the score value. Thus post_id and score are the two values I am looking for. The rank position of posts will be used in various places of my site.

Now what would be the correct and most efficient way to do this? Should I:

1. Just get all the post_ids and scores and store them in an array. Sort the array by score and get me the position of the desired postID in the array, which is the rank.

2. Create a separate table, fill it with the post_id and score sets and then sort the table. Update this table every time the score changes in a post and resort.

3. Create a separate table, fill it with the post_id and score sets and sort the table. Only update it periodically (like every 30 minutes) to hold the load down?

What I need this for is to have the score display in each post and also have a ranking widget use the ranking data. So it would be great if the data is always up to date (post score adjusted -> rank update). But if this is going to create a heavy load on the database/server I would be also fine if the data is not really up to date, but X minutes old.

As I am pretty new to databases I have a hard time figuring out what loads different approaches will create. Would be great if anyone could poke me in the right direction.

解决方案

Since ranking is not static process, it needs to be updated everytime there is an insert or an update into you table I would recommend you to handle that in query like :

SELECT @rownum:=@rownum + 1 as row_number, 
       t.*
FROM ( 
   < your ordered query goes here >
) t,
(SELECT @rownum := 0) r

这篇关于什么是数据排序/排序的正确和最有效的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！