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.