GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

troublesome "user's also took" query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

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

    Comment

    Working...
    X