| troublesome "user's also took" query [message #2692] |
Tue, 04 March 2008 12:49  |
jspath Messages: 3 Registered: January 2008 |
Junior Member |
|
|
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_id
FROM user_quiz uq, user_quiz uquq
WHERE uq.quiz_id = ?
AND uq.user_id = uquq.user_id
GROUP 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.
|
|
|
| Re: troublesome "user's also took" query [message #2751 is a reply to message #2692 ] |
Tue, 18 March 2008 02:15  |
debug Messages: 132 Registered: March 2008 |
Senior Member |

|
|
Unfortunately GROUP BY statement needs temporary table to be created, that's why you are getting Using temporary and Using filesort.
Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|