MySQL v3.x
PHP v4.x

I have a scoring table with almost 15,000 rows. Ranking a particular person is required.

I currently have this script to calculate the rank:

PHP Code:

$myScore = 233292;
$result = mysql_query("SELECT COUNT(*) as count FROM tbl WHERE score>$myScore");
$row = mysql_fetch_array($result, MYSQL_ASSOC);
echo "You rank $row[count].";   // You rank 123422. 


which... according to my web host, is causing excessive server load.

I'm wondering if there's any other way to optimize it?

thanks!

If you're using mysql I don't know, but in SQL Server you can query the information schema find what you need to know.

You could try building an index on the score column if you don't have one already. That'll change you from a table scan to an index scan. But the database is still going to parse through all the rows just to count them.

Your counting every row in the table. Like Learning Newbie said you can try creating an index on the score column.

Do you have a numbered column such as id? If so you can do a query for the last entry in the table.
select id from table order by id desc limit 1;
That will give you the last id number in the table.

I agree, create an index for the column field score and try something like this:

$result = mysql_query("SELECT COUNT(score) AS count FROM tbl WHERE score > $myScore");

I think that's where the problem is. I totally forgot to index the columns that I needed to rank. heheh...

Try a before and after with the index. ( You can drop it to go back to the before state. )