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
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.
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.