query running soo slow

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

query running soo slow

Post by GeXus »

Ok, I have the following query

Code: Select all

SELECT DISTINCT
user_log.quiz_id AS quiz_id,
quizes.name AS name,
quizes.canvas_url AS canvas_url,
answers.answer_profile AS answer,
answers.image_guid,
answers.description,
user_log.answer_id,
settings.show_profile,
settings.show_image
FROM
user_log
Inner Join quizes ON quizes.id = user_log.quiz_id
Inner Join answers ON answers.id = user_log.answer_id
Inner Join settings ON answers.quiz_id = settings.quiz_id
WHERE user_log.user_id = '123456'
GROUP BY quiz_id
ORDER BY user_log.created_dt asc

And it's running incredibly slow, If I remove the inner join on settings, it flys... but for some reason when I add that one join it goes incredibly slow, too slow..

I have indexes on

Settings.quiz_id
User_log.user_id
User_log.quiz_id
Answers.quiz_id



Any ideas if there is any specific I should be doing or looking out for?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you run an EXPLAIN on your query?
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

This is what I get..... Not really sure what actions to take on this though..

Code: Select all

+----+-------------+----------+--------+-----------------+---------+---------+-----------------------------------+-------+----------------------------------------------+
| id | select_type | table    | type   | possible_keys   | key         | key_len | ref                                      | rows  | Extra                                        |
+----+-------------+----------+--------+-----------------+---------+---------+-----------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | user_log| ref     | user_id,quiz_id | user_id  | 5       | const                             |    90 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | answers|eq_ref| PRIMARY,quiz_id| PRIMARY| 4       | froonie_quizes.user_log.answer_id |     1 |                                              |
|  1 | SIMPLE      | quizes   |eq_ref| PRIMARY            | PRIMARY| 4       | froonie_quizes.user_log.quiz_id   |     1 |                                              |
|  1 | SIMPLE      | settings| ref      | quiz_id              | quiz_id  | 5       | froonie_quizes.answers.quiz_id    | 26666 | Using where                                  |
+----+-------------+----------+--------+-----------------+---------+---------+-----------------------------------+-------+----------------------------------------------+
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It may be best to perform two queries then attach them after the fact.

The slow down is due to the exponential nature of joining. When you join a table that has 26666 entries that have to be looked at, it will markedly increase the time required to join.

I would remove the inner join against settings from this query. In a separate query hit against just settings using the IN() statement which in turn uses data collected from your first query results.
Post Reply