I'm having a bit of a problem with a query that takes a very long time (up to 1
minute) when many matching rows are found.
The tables are all indexed and the explain seems to indicate that mysql is using the
indexes but it is still painfully slow:
mysql> SELECT COUNT(TP.thread_id) AS num_posts, MAX(TP.post_date) AS
latest_date,T.thread_id, T.thread_name, i.post_image
FROM category AS C
-> INNER JOIN thread_link AS TL USE INDEX(CAT_INDEX) ON C.category_id =
TL.category_id
-> INNER JOIN thread AS T ON TL.thread_id = T.thread_id
-> INNER JOIN thread_image AS i ON TL.thread_id = i.thread_id
-> INNER JOIN thread_post AS TP USE INDEX(thread_id) ON TL.thread_id = TP.thread_id
-> WHERE C.category_id = '8759' AND i.display_type = 'thumbnail' AND TP.rating
> 0
-> GROUP BY TL.thread_id
-> ORDER BY 'num_posts' DESC
-> LIMIT 0 , 20;
... results ...
20 rows in set (37.37 sec)
The above query is a hypothetical query (hint: I'm not doing a forum db) but it pretty
much matches what I'm doing.
In this case there are many categories 'C' and a thread 'T' can be in multiple
categories that link together with a thread_link 'TL'. Thread posts 'TP' contain the
individual posts within a thread topic.
I want to select the top 20 thread topics 'T', for a particular category based on the
number of posts within that thread 'TP'. I also want to calculate the latest post
date (when the last post was added).
The tables work fine, the results are fine... it just ISN'T FAST! Especially if there
are a lot of threads for that particular category.
Here is the explain data:
+-------+--------+---------------+------------+---------+---------------------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+------------+---------+---------------------+------+---------------------------------+
| C | const | PRIMARY | PRIMARY | 4 | const | 1 |
Using temporary; Using filesort |
| TL | ref | CAT_INDEX | CAT_INDEX | 4 | const | 105 |
Using where |
| T | eq_ref | PRIMARY | PRIMARY | 4 | TL.product_id | 1 |
|
| i | eq_ref | PRIMARY | PRIMARY | 5 | TL.thread_id,const | 1 |
Using where |
| TP | ref | thread_id | thread_id | 4 | TL.thread_id | 2 |
Using where |
+-------+--------+---------------+------------+---------+---------------------+------+---------------------------------+
5 rows in set (0.00 sec)
I think the problem may be with 'Using Temporary; Using Filesort' probably due to the
GROUP BY???
Here are the indexes:
thread:
- thread_id (PK)
thread_link:
- thread_id, category_id (combined PK)
- category_id (CAT_INDEX)
thread_post:
- thread_id, post_id (combined PK)
category:
- category_id
thread_image:
- thread_id,display_type (combined PK)
- thread_id (INDEX)
Major kudos to whom ever can help me out with this!!
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
