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.
Best Answer
I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:
The plan I get is:
See the SQLfiddle