Server can't cope with ranking system

brattt

Hi. I am using the following code to get a rank from the 'Scores' of a number of poker players.

SELECT A.MemberID, A.FirstName, A.Surname, A.Sex, A.State, A.Region, A.Points, A.Matches, COUNT(B.points) + 1 AS rank
FROM tbl_players A LEFT JOIN tbl_players B ON B.Points > A.Points

This method works fine with just a few records, however I have at present almost 6000 players and it is taking about 2 minutes for the page to load with the results when I use the above method to work out a rank.

Is there another way I could do this without putting such a strain on the server?

etully

How often do the ranks change? You could perform the query once when the ranks change and write the list to a simple table. Then each display of the page is reading from the simple table instead of rebuilding the same list every time.

brattt

The ranks change daily as tournaments are played throughout Australia every day.

Weedpacket

It shouldn't be taking anywhere near as much as two minutes for just six thousand records; what are you using for a dbms, an abacus?🙂

Is there an index on the Points column?

I also suspect that an inner join rather than a left join would be more what you want: you are not going to be interested in B teams that have null points.

I note you're saying that it's taking "2 minutes for the page to load". Does this page list all six thousand players? If that's true, then it's not the database's fault things are so slow.