Ranking system

[MattG]

First I'll give a bit of background on the current script and database and then my question I need help with is at the bottom.

For one PHP/MySQL application I've developed I have a table of people and a very basic ranking system for each person.

There are currently these fields...

3 acting as counters for the overall, weekly and daily points.
1 recording the current daily rank
1 recording the previous daily rank
1 recording last week's rank.

At the end of each day a cron job updates the daily fields. It sets the previous rank from the current rank, replaces the current rank based on the points giving their new rank and resets the daily point tally. At the end of each week the same happens for the weekly rank and counter. The main counter keeps track of the all-time points.

This is all working fine but I'd now like to develop it further which is where my question comes in...

I would like to keep track of these different ranks so that the changes can be displayed via a PHP script over say the week,month,multiple months or past year however I'm not sure the best way of keeping this data.

My first thought was to add the data to a table each day but with 4,000 people in the database that's roughtly 1.5m rows per year to keep track of every rank of each day and as the amount of people in the database is constantly increasing so would that number.

My other idea was 2 separate tables, one tracking the rank of each day in the current month and a second table tracking only the data over the month, this would reduce the amount of rows to roughly 168,000 per year instead of 1.5m (120k in the 1st table, 48k in the 2nd).

To stop waffling and get to the point I was wondering if anyone could give me some suggestions about what the best way to go about it might be. I'm currently thinking of going with the second option but I thought I'd see what some more experienced people with different ideas might have to suggest.

Thanks in advance.

[johanafm]

Well, there is no way around storing the amount of data you describe if you want to retain resolution. One option would be to reduce resolution after a certain time. I.e. store daily points for the last N months only, for data a bit older than that, store monthly summaries, and for even older data, store year summaries.

Also, with proper indexing, 1.5 million rows is generally nothing. Still, if the DB is queried a lot to sum points and turn them into ranks, you could create views containing sum(points) and rank.

[MattG]

Thanks Johana, I think I will take the option where it reduces resolution after a certain time. After all there's unlikely to have a need for finding out exactly what the rank was 5 years ago today but I guess I will have to site and think about exactly what way to do it.

Thanks.