当前位置: 动力学知识库 > 问答 > 编程问答 >

MySQL - Bottleneck - Join one to many, Tags of news articles

问题描述:

I have a news section. Articles are tagged and stored in 3 tables:

posts:

posts_id,title,body

tags:

tags_id,name

posts_tags:

posts_id,tags_id

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: