Im using MySQL 4.1
I have a table like this:
CREATE TABLE `UserQuiz` (
`id` int(11) NOT NULL auto_increment,
`quizId` int(11) NOT NULL default '0',
`userId` int(11) NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`timeSeconds` int(11) NOT NULL default '0',
`timeMicro` int(11) NOT NULL default '0',
`score` int(11) NOT NULL default '0',
`correct` int(11) NOT NULL default '0',
`finished` enum('n','y') NOT NULL default 'n',
`finishTime` decimal(10,2) NOT NULL default '0.00',
`IP` varchar(16) NOT NULL default '',
`validUser` enum('n','y') NOT NULL default 'n',
PRIMARY KEY (`id`),
KEY `userId` (`userId`,`score`,`finished`),
KEY `quizId` (`quizId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
This query is going very slow, as in scanning all rows. What is does is find the 10 best scores, where each user ID can only be on the list once each.
I tried to look at optimizing:
http://dev.mysql.com/doc/refman/4.1/en/group-by-optimization.html - But I couldn't solve my problem, even after reading it.
So I hope some of you bright people can help me in a way to get the 10 highest scores out :)
The query looks like this:
SELECT *,MAX(score) AS maxscore
FROM UserQuiz WHERE UserQuiz.quizId=1
GROUP BY UserQuiz.userId ORDER BY maxscore DESC LIMIT 10
Thanks
Lasse :)