Speed up highscore rank calculation I calculate a highscore and find the users current rank like shown here. This is the query:

SELECT * FROM (SELECT  q.*, @r := @r + 1 AS rank
FROM ( SELECT  @r := 0 ) vars,
(
    SELECT  p.*, MAX(punkte) AS max_points
    FROM olympia_punkte_alle p
    WHERE markt_id = 1
    GROUP BY fb_id
    ORDER BY max_points DESC
) q) AS q2 WHERE fb_id = '26950' ORDER BY max_points DESC LIMIT 1


Problem is that the table has many rows (~8,5 Mio) and so this query is very slow. I could cache the sub select but than the highscore is always kind of old.

Any thoughts on how to make this faster?

EXPLAIN:

id  select_type         table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY &lt;derived2&gt;  ALL     NULL    NULL    NULL    NULL    1352    Using where; Using filesort
2   DERIVED &lt;derived3&gt;  system  NULL    NULL    NULL    NULL    1    
2   DERIVED &lt;derived4&gt;  ALL     NULL    NULL    NULL    NULL    1352     
4   DERIVED             p       ref     markt_id 4              2055    Using where; Using temporary; Using filesort
3   DERIVED NULL        NULL    NULL    NULL    NULL    NULL    NULL    No tables used
What about this query:

SELECT COUNT(*) + 1 as user_rank
    FROM olympia_punkte_alle p
    WHERE markt_id = 1
    GROUP BY fb_id
    HAVING 
        MAX(punkte) &gt; (SELECT MAX(punkte)
            FROM olympia_punkte_alle
            WHERE markt_id = 1
            GROUP BY fb_id
            HAVING fb_id = 2950)


We are getting the count of users that have better score then users with id 2950.
The count of better users + 1 is rank of your user.