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

SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
FROM single
GROUP BY user_id, stage

table engine is innodb, plus I haven't use any indexes except one key which is game_id it takes 0.16 sec to execute the query on 250k rows.

well I added INDEX(user_id_stage) to my indexes and execution time reduced to 0.07 sec and EXPLAIN SELECT shows that mysql uses my index, but when I run this query, mysql don't use the index, what's the problem here? what am I doing wrong?

SELECT user_id, 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 single
            GROUP BY user_id,stage
        ) AS ts
GROUP BY user_id

EDIT: I mixed up the order of my group bys sorry, and there's another group by in the outter select hich I also forgot

here's table defenition:

`single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`user_id`,`stage`)
)

this table stores savegames for players, user_id comes from members table. each member can play a stage as much as he wants, then I need a ranklist which requires max highscore of each stage for each member, then sum all of highscores for each member and sort them. so a member can play for example 100 times each stage, and I want to show this in my ranklist, that's why I'm counting total attemps of a stage for each member for each stage, and then sum these values for each member in order to get that value.

here's EXPLAIN SELECT for second query EXPLAIN SELECT results

I use user_id to inner join this table with members table to get member's username.

share|improve this question
1  
Your GROUP BY in the first example is in the opposite order from the GROUP BY in the second example... so you likely need an index in the correct order (stage,user_id) for the second query... but you should post EXPLAIN SELECT as well as the table definition, because there's not enough information here for a definitive answer. It's also hard to tell what your 2nd query is trying to calculate and whether it's accurate for its intended purpose. The user_id you get from that query does not appear to be meaningful and total_players is the total of records in single. –  Michael - sqlbot Feb 17 '13 at 22:33
    
@PaulWhite: thanks, execution time reduced to 0.38 sec :D –  Nero Feb 18 '13 at 12:22
    
Oh good. I converted my comment to an answer. –  Paul White Feb 18 '13 at 12:38
1  
You changed the order by in the subquery, which is good. Because an (user_id, stage, highscore) index can be used and it makes more sense to use that one. –  ypercube Feb 24 '13 at 20:09
add comment

1 Answer 1

up vote 3 down vote accepted

I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:

create table `single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`stage`,`user_id`, `highscore`)
)

The plan I get is:

Explain

See the SQLfiddle

share|improve this answer
    
thanks for your time. do you know anyways to use an index for outter select too? I searched a little and found out that it can be possible with creating a view, but I couldn't work it out –  Nero Feb 18 '13 at 19:06
    
Sorry I have no idea - MySQL is really not my thing. Perhaps ask a new question for that? –  Paul White Feb 18 '13 at 22:54
    
thank man your answer has been really helpful –  Nero Feb 19 '13 at 10:28
add comment

Your Answer

 
discard

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

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