Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5) I was wondering if there is any way to enhance this query so that it executes faster? This query returns 10K rows.

SELECT @n := @n + 1 rank,user_id, username, highscore, stage, total_played
FROM (select @n:=0) initvars, (
    SELECT m.user_id, m.username, SUM(ts.highscore) AS highscore, MAX(ts.stage) AS stage, total_played
    FROM (
        SELECT user_id, MAX(highscore) AS highscore, stage
        FROM games
        INNER JOIN single_player
        GROUP BY stage,user_id
        ) AS ts
        SELECT user_id, COUNT(*) AS total_played
        FROM single_player
        GROUP BY user_id) AS tc
    INNER JOIN members AS m
    GROUP BY user_id
    ORDER BY stage DESC, highscore DESC) as r

Edit: Raad this the result you asked for :)


I changed the query a little bit, execution time reduced to 0.15 sec!!!

    SELECT @n := @n +1 rank, user_id, username, highscore, stage, total_played
    FROM (SELECT @n :=0)initvars, (

            SELECT user_id, username, SUM( ts.highscore ) AS highscore, MAX( ts.stage ) AS stage, SUM( count ) AS total_played
            FROM (

                SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
                FROM games
                INNER JOIN single_player
                USING ( game_id ) 
                GROUP BY user_id, stage
            ) AS ts
        INNER JOIN members
        USING ( user_id ) 
        GROUP BY user_id
        ORDER BY stage DESC , highscore DESC
    ) AS rank
) AS final
LIMIT {$limit}
OFFSET {$offset}

$limit in most cases is 10 I ran this on a core i5 2.5GHz cpu and got these results I just added my primary & unique keys, so can I make it even faster? Like declaring some columns as INDEX?

share|improve this question

migrated from Feb 15 '13 at 14:45

This question came from our site for professional and enthusiast programmers.

Maybe you could add the CREATE TABLE statements to your question for all participating tables for greater success. – biziclop Feb 14 '13 at 14:37
Can you post up the results of running EXPLAIN SELECT...? – Raad Feb 14 '13 at 14:37
@biziclop: I did that but it got 1.8 MiB to store the whole results to a table, and as you can see I just joined it whith table members that makes it even bigger – Nero Feb 15 '13 at 6:46
Publish the datamodel and the EXPLAIN result, it will more simple – sdespont Feb 15 '13 at 6:47
one more thing, I didn't complete relations yet, can that be the problem? – Nero Feb 15 '13 at 7:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.