MySQL Forums
Forum List  »  Optimizer & Parser

Help with optimizing GROUP BY query
Posted by: lasse
Date: June 12, 2006 07:16AM

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 :)

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with optimizing GROUP BY query
5054
June 12, 2006 07:16AM
2443
June 12, 2006 10:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.