Slow Query Problem for calculating a Ranking - MySQL 5.0.15

[StarBuG]

Hi

I've got a SQL query to calculate a ranking based on points a user has.
This query performs good when there're just a few rows in the table.
But when i try the query with more rows it gets realy slow. I am using mysql 5.0.15

here are some "benchmarks":
10 rows = 0,018 sec
1000 rows = 1,520 sec
2000 rows = 6,337 sec
5000 rows = 22,680 sec

How can i optimize the query ?

-- the table with the users and the points
DROP TABLE IF EXISTS points;

CREATE TABLE points (
user_id bigint(20) unsigned NOT NULL,
points int(10) unsigned default '0',
PRIMARY KEY (user_id),
KEY points (points)
) ENGINE=InnoDB;

-- index for the points column (seems useless ...)
create index i_points on points ( points );

-- 10 rows of demo data
INSERT INTO points (user_id, points) VALUES
(1,111), (2,111), (3,132), (4,13), (5,2511),
(6,51), (7,261), (8,121), (9,91), (10,981);

-- and heres the query that need optimization
SELECT U1.user_id, U1.points,
COUNT(U2.points) + 1 AS rank
FROM points AS U1
LEFT OUTER JOIN
points AS U2
ON ( U2.points > U1.points)
WHERE U1.user_id > 0
GROUP BY U1.user_id
ORDER BY rank

Another approach would be:

SET @rank:=0;
SELECT user_id,points, IF(@last_points!=points,@rank:=@rank + 1,@rank) as rank, @last_points:=points FROM points ORDER BY points DESC;

But is there any chance to put these to queries into *one* view ?

If anyone has an Idea for how to improve the performance or has a much better approach I would be realy greatfull.

Thank you in advance

StarBuG

[Marco van Herwaarden]

1. Why use InnoDB?

2. How are you processing the results? If a PHP script is using them, why not calculate the rank there, would save you an expensive join.

3. Limit the query to the number of rows you want to use.

[StarBuG]

1. because all my tables use innodb (transactions, foreign keys)
2. the query is actualy a view. i need to join against it. that´s why i can´t do it in php or whatever.
3. that doesn't help, because of 2.

[Marco van Herwaarden]

The only reason for that join is to calculate the rank if i am correct.

[StarBuG]

that is true

[Marco van Herwaarden]

Then better handle rank-counting while reading the rows in your php script.

[StarBuG]

i think you don´t understand the problem but thank you for trying

[Marco van Herwaarden]

Then make me understand :D

PS Changing the ORDER BY to ORDER BY points DESC could also improve performance since it can use the index and don't need to do a sort using a temp table.

[StarBuG]

thank you, but this doesn?t change the time the query needs ;)