Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question

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

 
discard

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

Browse other questions tagged or ask your own question.