Help! Trying to making my database sorting a bit smarter

AARRRGH

Hello

A website I am working on - http://www.babes.ie - allows people to vote bebo users up and down. It's just a bit of fun more than anything else.

I've a little problem though...

Currently my ranking system is based on a simple select -

select * from users order by votes desc

So you can see I don't actually store anyones rank in the database, I just display everyone and order it by the number of votes.

However I would like to either store the ranks or be able to figure out someones rank on the fly.

As this website is just a bit of fun, I don't really want to have to recalculate everyones rank after each vote (potentially quite processor intensive).

Is there a clever way I can do this?

Any help or pointers greatly appreciated.

Thank you.

seamus

I'm not sure if there's any inbuilt way of processing a rank straight off, but you could maintain a separate table contain user:rank pairs. Then add an AFTER UPDATE trigger to the main table which recalculates the separate table after every update.

I say use a separate table purely because that's the way I see it in my head. Maintaining the rank in the same table is definitely possible.

Computing ranks on-the-fly can also be improved a little if you chop off some results. For example, to get the rank of an individual, you can call;

SELECT count(ID) from users where votes > (select votes from users where id = 500)

This will give you a figure for the number of people who have more votes than this person. So their rank is this + 1.

AARRRGH

Thank you Seamus. That's very helpful.

ron_darrell

I'm assuming as you can perform an SQL command that you have scripting ability available. There are 2 options you go for here.

1. If you're using ASP (it may also be possible in PHP but I have never tried it so I will only state it categorically for ASP) you could use an Application variable to store the rankings and get the page to update on a weekly/daily/hourly basis.

2. You could write the rankings to a text/xml file and update on a weekly/daily/hourly basis

The benefits of these 2 methods is that instead of querying the database each time someone is looking for a ranking you are accessing either the text file or application variable (thus freeing up your resources)

If you don't like either of these options then the option offered by our colleague (a separate table to hold the rankings) makes the next best sense however you still have server overhead in accessing the database.

-RD

seamus

ron_darrell wrote:

2. You could write the rankings to a text/xml file and update on a weekly/daily/hourly basis

Indeed, I had considered this and initially dismissed it but it could actually work out better. If the rankings were recalculated, say on a daily basis and the site becomes quite popular, then there could be a lot of interest in "publishing" the new rankings each morning.

If you don't like either of these options then the option offered by our colleague (a separate table to hold the rankings) makes the next best sense however you still have server overhead in accessing the database.

My main concern would be the index overhead with the amount of updates.

I recommended a separate table to restrict the amount of locking - the main users table doesn't need to be locked while the ranks are being calculated. In addition to this, the index only needs to be maintained for each vote++ on a row, as opposed to having an update called on every row in the users table.

Given that it's a voting system, if it becomes very popular then you could have updates occuring very frequently. Assuming that you build indexes on the rankings table, the index would theoretically need to be reorganised every time someone casts a vote, it could work out very expensive.

This may all be thinking a bit too much about it though. Any idea how may possible rows in the users table dublindude?

As an aside, if you have access to a MSSQL 2005 DB, and you want to have a bit of a flick around in it, this includes a RANK() function that will do all the work for you. Your query would look like this:

SELECT ID, name, votes, RANK() OVER (ORDER BY votes DESC) as 'Rank' FROM users

WeeDiddly

Also, just a heads up, if you're going to be sending high traffic onto Bebo from the domain name "babes.ie", Bebo will get suspicious, and without even barely looking, block your website from entering their's, it has happened before with other websites in america linking into Bebo.

My suggestion is let them know you will be licking to them, and they'll have no problem with it. Just a heads up for you!

Good Luck,
Nev