Home » Performance » MySQL » troublesome "user's also took" query
troublesome "user's also took" query [message #2692] Tue, 04 March 2008 12:49 Go to next message
jspath  is currently offline 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 Go to previous message
debug  is currently offline 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
Previous Topic:How to get last inserted record?
Next Topic:Depressing bad MYSQL performance
Goto Forum:
  


Current Time: Sat Jul 4 03:25:57 EDT 2009

Total time taken to generate the page: 0.41799 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.