Complex MySQL Query is Slow

Question

A program I've been working on uses a complex MySQL query to combine information from several tables that have matching item IDs. However, since I added the subqueries you see below, the query has gone from taking under 1 second to execute to over 3 seconds. Do you have any suggestions for what I might do to optimize this query to be faster? Am I wrong in my thinking that having one complex query is better than having 4 or 5 smaller queries?

    SELECT uninet_articles.*, 
           Unix_timestamp(uninet_articles.gmt), 
           uninet_comments.commentcount, 
           uninet_comments.lastposter, 
           Unix_timestamp(uninet_comments.maxgmt) 
    FROM   uninet_articles 
           RIGHT JOIN (SELECT aid, 
                              (SELECT poster 
                               FROM   uninet_comments AS a 
                               WHERE  b.aid = a.aid 
                               ORDER  BY gmt DESC 
                               LIMIT  1) AS lastposter, 
                              Count(*)   AS commentcount, 
                              Max(gmt)   AS maxgmt 
                       FROM   uninet_comments AS b 
                       GROUP  BY aid 
                       ORDER  BY maxgmt DESC 
                       LIMIT  10) AS uninet_comments 
                   ON uninet_articles.aid = uninet_comments.aid 
    LIMIT  10

As a shorter answer, No and yes - I don't have specific suggestions, but you are incorrect in thinking that one complex query is always better than multiple small queries. — David Manheim, Jun 27 '12 at 15:45

score 0 · Accepted Answer · edited May 23 '17 at 10:09

Queries can be though of as going through the data to find what matches. Sub-queries require going through the data many times in order to find which items are needed. In this case, you probably want to rewrite it as multiple queries. Many times, multiple simpler queries will be better - I think this is one of those cases.

You can also look at if your indexes are working well - if you know what that is. The reason why has to do with this: How does database indexing work?.

For a specific suggestion, you can find the last poster for each AID in a different query, and simply join it afterwards.

mihaisimi · Answer 2 · 2012-06-27T16:02:17.623

0

It always depends on the data you have and the way you use it.

You should use explain on your selects to see if you are using the indexes or not. http://dev.mysql.com/doc/refman/5.5/en/explain.html

edited Jun 27 '12 at 16:02

answered Jun 27 '12 at 15:44

mihaisimi

1,911
13
15

Complex MySQL Query is Slow

2 Answers2