index doesn't work properly

Question

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.

Answer 1

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:

See the SQLfiddle