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
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
show 4 more comments

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

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

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.