MySQL help - I'm stuck
 

I'm stuck on how to set the popularity / rank for MySQL records.

Let's say I have 10,000 videos, each video has a viewcount. To select them in order of viewcount:

SELECT * FROM videos ORDER BY viewcount;

[0] -> video A with 13,000 views
[1] -> video B with 12,824 views
[2] -> video C with 11,374 views
etc...

But what I want to do, is SET the index for each video based on that sort order so I can retieve it later:

SET video A rank = 0
SET video B rank = 1
SET video C rank = 2
etc...

This way, if I just view video C (mysite.com/video-c.html), I know that it is the 3rd most popular video. I don't want to do an entire SELECT to find out what rank it is in the sort order.

I'm stuck on how to do this in one MysQL statement...

I got this far:

SELECT @rownum:=@rownum+1 'bd_rank', u.bd_id, u.bd_likes FROM bd_user u, (SELECT @rownum:=0) r ORDER BY bd_likes DESC

http://img714.imageshack.us/img714/6326/rankh.gif

Now I just need to figure out how to:

UPDATE videos SET videos.bd_rank=results.(SELECT same above) WHERE videos.bd_id=results.bd_id

...or something like that. I'm not good with compound statements.

Lets say you had 300,000 videos...................

It wouldn't work that great no matter how you did it.
(assuming you want the info updated in real time)

So why even go there if you plan on growing?


Use old school programming to record views in a file then sort the file on views and
always delete the last entry after sorting if the file is larger that 10,000 videos.

Then use that file to display the vids by views.
That gives you realtime data.

You current videos with the most views will not show up in the file until they are
clicked on again, but so what? If these are really the best videos then they will
be clicked again very soon.

Changing 10,000 to 1000 in the above lets you realize just how fast you can make this
page load.

Here is the result mysql statement I got that sets rank:

UPDATE bd_user i, (SELECT @rownum:=@rownum+1 'bd_rank', u.bd_id FROM bd_user u, (SELECT @rownum:=0) r ORDER BY bd_likes DESC) q SET i.bd_rank=q.bd_rank WHERE i.bd_id=q.bd_id;

I like what you wrote and will re-read it to make sure I understand it.

I also realized, that I don't need to rank the entire database - I only need to check the rank of "the entry before this one" and swap them if the count reverses.

ie:

video A = 45
video B = 44

View B:

video A = 45
video B = 45 (no swap)

View B again:

video A = 45
video B = 46 (swap now, so B is above A)

=>

video B = 46
video A = 45

SELECT * FROM videos ORDER BY viewcount DESC;

Result:
[0] -> video A with 13,000 views
[1] -> video B with 12,824 views
[2] -> video C with 11,374 views


SELECT * FROM videos ORDER BY viewcount ASC;

Result:
[2] -> video C with 11,374 views
[1] -> video B with 12,824 views
[0] -> video A with 13,000 views

tbh would be easier and use less cpu time to just run a script every day|hour|etc to do a select to work out all the rankings, then update each field with its new rank.

no need really to calculate the rank every time it is requested. (unless the site / prog needs it to be real time)

Can't find my own code for where I did something like this but I know it works.. Whether you can do it or not depends on your version of mysql.. You need to add a "rank" field to your videos table as well.. Then it's something like this