I have a news section. Articles are tagged and stored in 3 tables:
The problem at the moment is that the front page of the news section displays previews of the first 10 articles. Each preview displays the tag lists. Displaying the tag lists means I have to query again for each post to get a list of the tags.
So for example, the 10 previews requires, 1 query(gets 10 posts from posts)+10 queries(1 for each post on tags and posts_tags to get tag lists). 11 queries to load a page seems like it could eventually be a bottleneck.
Should I forgo tagging previews? Would PIVOT result in the same amount of queries?
You can use a "join" to keep it all in one query. However: these kind of queries can get very "expensive", escpecially if you are going to do some kind of grouping. So i would recommend to experiment with subselects. For example:
SELECT ( SELECT GROUP_CONCAT(name) FROM tags, posts_tags WHERE posts_tags.posts_id = posts.posts_id AND tags.tags_id = posts_tags.tags_id ) FROM posts LIMIT 10
With such kind of subselects you can normally get much better results than doing a normal join and grouping.
Caching is of course still a good idea ...
I see 2 main solutions you could try:
Make your code to do all in one query (return each post with related tags using a join)
Use a Cache layer between the web server and the scripting language, so if the page doesn't change, it doesen't makes all the querys again