Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What indexes do you have on the tables? Can you add the tables' definitions?
–
ypercubeFeb 28 '12 at 7:09

I added another refactored query for you to try !!!
–
RolandoMySQLDBAFeb 28 '12 at 17:51

As a sidenote, you should run ALTER TABLE post_tags DROP INDEX post_id; and then ALTER TABLE post_tags DROP INDEX tag_id; to save space. They are redudndant indexes.
–
RolandoMySQLDBAFeb 28 '12 at 20:58

Thanks for reply! Looks like I need 5 more hours to be able to test that. I am using NOT IN() because I don't want to view the tags that are already selected. But it is possible to delete them afterwards if it is faster to do that way.
–
HenriFeb 27 '12 at 13:46

My first suggestion would be to change the COUNT(DISTINCT pt1.tag_id) = 2 to COUNT(*) = 2. Assuming that the (post_id, tag_id) combination is unique in table posts_tags, the results will be the same (and faster).

Thank you for a reply! I am able to test this in 8 hours. I also need to make sure that this query performs well with for example 10 tags. That is going to be a pretty long query.
–
HenriFeb 27 '12 at 8:20

UPDATE 2012-03-01 14:05 EDT

SELECT B.id,B.name,A.kpl
FROM
(
SELECT pt2.tag_id,COUNT(*) kpl
FROM
(
SELECT post_id,count(tag_id)
tagcount FROM posts_tags
WHERE tag_id IN (88,5)
GROUP BY post_id
HAVING COUNT(tag_id)=2
) Matchingposts
INNER JOIN
(
SELECT pt.tag_id,pt.post_id FROM posts_tags pt
LEFT JOIN
(
SELECT post_id,count(tag_id)
tagcount FROM posts_tags
WHERE tag_id IN (88,5)
GROUP BY post_id
HAVING COUNT(tag_id)=2
) ptkeys
USING (post_id)
WHERE ptkeys.post_id IS NULL
) pt2
USING (post_id)
GROUP BY pt2.tag_id
HAVING count(*) > 10
) A
INNER JOIN tags B USING (tag_id)
;