[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