# troublesome "user's also took" query

**URL:** <https://forums.percona.com/t/troublesome-users-also-took-query/661>\
**Category:** Other MySQL® Questions\
**Created:** [March 4, 2008, 12:19pm UTC](https://forums.percona.com/t/troublesome-users-also-took-query/661 "2008-03-04T12:19:39Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jspath](https://avatars.discourse-cdn.com/v4/letter/j/f08c70/32.png) [@jspath](https://forums.percona.com/u/jspath)\
**Post date:** [March 4, 2008, 12:19pm UTC](https://forums.percona.com/t/troublesome-users-also-took-query/661/1 "2008-03-04T12:19:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![debug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/debug/32/772_2.png) [@debug](https://forums.percona.com/u/debug)\
**Post date:** [March 18, 2008, 12:45am UTC](https://forums.percona.com/t/troublesome-users-also-took-query/661/2 "2008-03-18T00:45:24Z")

</div>

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