What is the correct and most efficient approach fo

Question

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.

Answer 1

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