Quote:
Originally Posted by camperjohn64
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...
|
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.