Re: Related posts by tags optimization

[René Olivo]

Hello Rick,

Thanks for your reply. Unfortunately, I don't think this is the right solution. I couldn't get your whole code, I tried it running it like this:

SELECT GROUP_CONCAT(t.tag) AS tags, COUNT(*) AS relationshipCount, p.id, p.title
FROM
( SELECT idTag, COUNT(*) AS relationshipCount
FROM rel_posts_tags
WHERE idPost = 34501
GROUP BY idTag
ORDER BY relationshipCount DESC
LIMIT 10 ) AS rptMain
JOIN rptMain ON rptMain.idTag = rptMain.idTag
JOIN rel_posts_tags AS rpt ON rpt.idPost = p.id
JOIN posts AS p ON rpt.idPost = p.id
JOIN tags AS t ON rpt.idTag = t.id
WHERE p.id != 34501
GROUP BY p.id

(http://sqlfiddle.com/#!2/3d163/19)


And it's in fact giving me 2 USING TEMPORARY; USING FILESORT; instead of 1.

I'm not sure how a sub query would help in this case, could you explain me the reasoning of it to achieve improvement?

Thank you :)


Note: I don't think the sub-query you are using in this case is necesary as it only brings the tags of the main post and the relationship count will always be 1.