Tags - table design and queries

sidsel

So far I have made a working tags solution with three tables:

Tags_posts

post_id | tag_id

Posts

post_id | ....

In ´Tags´ I have one tag per row. Table ´Tags_posts´ connect tags with posts.

The following query works fine but can it be optimized?

SELECT posts.*, GROUP_CONCAT(tags.tag_name ORDER BY tags.tag_name) AS tags 
FROM posts LEFT JOIN tags_posts on tags_posts.post_id = posts.post_id 
LEFT JOIN tags on tags.tag_id = tags_posts.tag_id  

where posts.post_id  = 110192 
GROUP BY posts.post_id

If I want to find related posts with more or less the same tags and sort by relevance the query below works but I'm not sure it's the optimal way of doing it:

SELECT posts.*, GROUP_CONCAT(tags.tag_name ORDER BY tags.tag_name) AS tags , COUNT( tags_posts.tag_id  ) 
FROM posts LEFT JOIN tags_posts on tags_posts.post_id = posts.post_id 
LEFT JOIN tags on tags.tag_id = tags_posts.tag_id where tags.tag_name IN ('blow', 'fish', 'monkey')

GROUP BY posts.post_id

ORDER BY COUNT( tags_posts.tag_id  ) DESC

Your comments or ideas for improvements are welcome

walterquez

You can add "DISTINCT" inside GROUP_CONCAT(), that way it will skip duplicates, unless you want them included.

GROUP_CONCAT(DISTINCT tags.tag_name ORDER BY tags.tag_name)

sidsel

If I understand you correct it shouldn't be necessary as I store only one tag per row and only "unique" tags hence the tag_id.