troublesome "user's also took" query

I’m trying to optimize a query on the following table:

The user_quiz table looks like:

±--------------±---------±-----±----±--------±------+| Field | Type | Null | Key | Default | Extra |±--------------±---------±-----±----±--------±------+| user_id | int(12) | NO | PRI | NULL | || quiz_id | int(12) | NO | PRI | NULL | || activity_date | datetime | NO | | NULL | |±--------------±---------±-----±----±--------±------+

The primary key is on user_id, quiz_id and there is an additional index on quiz_id.

The point of the query is to retrieve quizzes related to a particular quiz, sorted by how many users took both. The query looks like:

SELECT uquq.quiz_idFROM user_quiz uq, user_quiz uquqWHERE uq.quiz_id = ? AND uq.user_id = uquq.user_idGROUP BY uquq.quiz_id ORDER BY COUNT(*) DESC

The explain produces:

±—±------------±------±-----±----------------±--------±--------±----------------±------±---------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±-----±----------------±--------±--------±----------------±------±---------------------------------------------+| 1 | SIMPLE | uq | ref | PRIMARY,quiz_id | quiz_id | 4 | const | 14126 | Using index; Using temporary; Using filesort || 1 | SIMPLE | uquq | ref | PRIMARY | PRIMARY | 4 | quiz.uq.user_id | 13 | Using index |±—±------------±------±-----±----------------±--------±--------±----------------±------±---------------------------------------------+

This query seems to cause extreme load on our database server to the point where we’ve had to disable it.

I’d appreciate any insight you guys could offer, like how I might avoid the filesort. I have more info I could provide, but wasn’t sure what might be applicable.

Unfortunately GROUP BY statement needs temporary table to be created, that’s why you are getting Using temporary and Using filesort.