1

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
        USING(game_id)
        GROUP BY stage,user_id
        ) AS ts
    INNER JOIN (
        SELECT user_id, COUNT(*) AS total_played
        FROM single_player
        GROUP BY user_id) AS tc
    USING(user_id)
    INNER JOIN members AS m
    USING(user_id)
    GROUP BY user_id
    ORDER BY stage DESC, highscore DESC) as r
ORDER BY rank

Edit: Raad this the result you asked for :)

Result of EXPLAIN SELECT

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

SELECT *
FROM (
    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?

migrated from stackoverflow.com Feb 15 '13 at 14:45

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

  • 1
    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
  • 2
    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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.