2
\$\begingroup\$

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 <derived2>  ALL     NULL    NULL    NULL    NULL    1352    Using where; Using filesort
2   DERIVED <derived3>  system  NULL    NULL    NULL    NULL    1    
2   DERIVED <derived4>  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
\$\endgroup\$
3
  • \$\begingroup\$ Since rank is calculated on the outer query, is it really necessary here? Could this be calculated in php instead. \$\endgroup\$
    – Waygood
    Commented Aug 8, 2012 at 16:12
  • 1
    \$\begingroup\$ @Waygood: You want to load 8,5 millions records to memory in php script? \$\endgroup\$
    – ŁukaszW.pl
    Commented Aug 8, 2012 at 16:15
  • \$\begingroup\$ After re-reading, the OP just wants the current rank, not a list of rankings, which would have been row number (in php). +1'd @ŁukaszW.pl (you LOL) \$\endgroup\$
    – Waygood
    Commented Aug 8, 2012 at 16:19

2 Answers 2

2
\$\begingroup\$

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) > (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.

\$\endgroup\$
1
  • \$\begingroup\$ Nice, this query is 1 second faster! But still takes 6 seconds. I am trying to store max_points, that should make it faster. \$\endgroup\$ Commented Aug 13, 2012 at 11:22
1
\$\begingroup\$

This is the only refactoring that I can come up with:

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

A possible caching optimization that you could implement is to add a max_points column in some users table somewhere. Then every time a user changes his score, you calculate MAX(punkte) and store that in the max_points column. That way you could save time calculating the maximum points for every query. It would end up something like:

SET @r = 0;
SELECT * FROM (
    SELECT u.*, @r := @r + 1 AS rank
    FROM users u
    ORDER BY u.max_points DESC
) AS q
WHERE q.fb_id = '26950'
LIMIT 0,1
\$\endgroup\$
1
  • \$\begingroup\$ Storing max_points is a great idea. Thank you! \$\endgroup\$ Commented Aug 13, 2012 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.