Ordering by Popularity - mySQL and PHP

n1cked

I have a fairly large mySQL database to individual text files (guitar tabs).

I want to order a top 10 list of the files by popularity in my php document.

Currently i simply order my list by the number of views per file.

This is not satisfactory as newer files are typically going to have been viewed less that older files that have been there forever.

How would this best be done.

Surely it would be inefficient to process each database entry (views / time_since_posted). Is there a better way to do this.

Any Ideas?

The website in question is http://kiwitabs.derail.co.nz - browse by most popular

I cant find anything on the net with regards to popularity listing.

kakki

what about a ratio, age divided by viewings ( or other way around )

n1cked

Yeah, thats what i will probably do.

The reason i ask though is that i want to avoid it if i can. That requires the entire database to be processed (view/age) each time and then re-organised to display the top 10.

What i though might be beter was that something (magical) extra was written to the database each time the files are viewed. (same time as each view is recorded). This (magical) extra thing would some then be a single variable in the database that i can order my top 10 from.

I think i just answered my own query. I have the ratio worked out each time a file is viewed and written to db.

Sorry, its late (you know how it gets). 13 hrs nonstop on this site today. I can sleep now.

TF_TheMoon

You'd need to write code so that when your files are viewed they update the database with the results of the formula. This will make querying to pull out your populariaty list faster, but will slow down the load time of the page silghtly as it'll need to connect to the database and update the extra field every time someone views the page

n1cked

Yeah your right. Thanks.
The minimal slowing down is some what prefered over the large loading time of the entire list.
Ta. Problem solved.