Speed up MYSQL INNER JOIN Query Across Two Tables

Question

The purpose of this query is to act like a search. Unfortunately the query is currently running from 1.5 to 2 seconds which is unacceptable. After running EXPLAIN on the query I see it's using "Using temporary; Using filesort" and no indexes. However, I'm not exactly sure where you could put an index on this query.

The ORDER BY also slows the query down a whole lot as well but it's needed. Any suggestions on how to improve this query?

SELECT DISTINCT p.id, p.date,
(
  SELECT COUNT(post_id) FROM post_tags WHERE post_id = pt.post_id
) as t_count 
FROM post_tags pt 
INNER JOIN posts p 
  ON (pt.post_id = p.id) 
WHERE pt.t_id IN (7,456)
ORDER BY t_count, p.s_count DESC, p.id DESC 
LIMIT 0, 50;

Here is the EXPAIN statement: https://gist.github.com/e742982e435cf082c033

Is it not more efficient to use count(pt.post_id) and GROUP BY p.id and p.date? You can get rid of the subquery then for starters. — dash, Jan 25 '12 at 00:33
@AdrianCornish I've added the EXPLAIN statement to the post and here: https://gist.github.com/e742982e435cf082c033. — stwhite, Jan 25 '12 at 00:43
@dash Removing " SELECT COUNT(post_id) FROM post_tags WHERE post_id = pt.post_id" would remove a sort on the temp "t_count" column which is rather important with this query. — stwhite, Jan 25 '12 at 00:46
myisam or innodb ? if using mysiam read the following links about clustered indexes. http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/ and http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html and http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601 — Jon Black, Jan 25 '12 at 09:59

score 0 · Accepted Answer · answered Jan 25 '12 at 00:44

0

Rewriting as:

SELECT p.id
     , p.date
     , COUNT(*) AS t_count 
FROM posts p
  INNER JOIN post_tags pts 
    ON pts.post_id = p.id
WHERE EXISTS
      ( SELECT *
        FROM post_tags pt
        WHERE pt.post_id = p.id
          AND pt.t_id IN (7,456)
      )
GROUP BY p.id
ORDER BY t_count, p.s_count DESC, p.id DESC 
LIMIT 0, 50 ;

answered Jan 25 '12 at 00:44

ypercubeᵀᴹ

113,259
19
174
235

Can you add the EXPLAIN for this one? – ypercubeᵀᴹ Jan 25 '12 at 00:45
The explain for your query seems to be the same as my original query: https://gist.github.com/c97338f3abf10283955f – stwhite Jan 25 '12 at 00:51
Can you add the tables definitions and the indexes you have? – ypercubeᵀᴹ Jan 25 '12 at 00:52
Does `post_tags.t_id` have an index? – ypercubeᵀᴹ Jan 25 '12 at 00:52
After adding an index to post_tags.t_id it seems give a slight advantage, but not much. Ideally this query would be at least under 800ms. – stwhite Jan 25 '12 at 01:10
As a guess add a composite index on pt.t_id, pt.post_id and also on p.s_count,p.id – Adrian Cornish Jan 25 '12 at 01:30
1

@AdrianCornish Both of those indexes have already been set. It looks like the majority of the slowness is caused by the ORDER BY. – stwhite Jan 25 '12 at 03:32
That would make sense - since the ORDER BY has to been run totally to satisfy your LIMIT clause - out of interest - if you feel like replying again :-) - what mysql version are you using? – Adrian Cornish Jan 25 '12 at 03:56

score 0 · Answer 2 · answered Mar 10 '12 at 09:10

0

If you see "Using temporary;" in your exlain, that usualy mean you have not enought memory for your "order by" or other operation.

For this query i think you have increase in my.conf

sort_buffer_size=128M

answered Mar 10 '12 at 09:10

arheops

15,544
1
21
27

Speed up MYSQL INNER JOIN Query Across Two Tables

2 Answers2