duanchao5258
2016-04-26 12:33 阅读 23
已采纳

这是使用NOT IN的好方法吗?

Let us Imagine the Facebook homepage. There is a list of posts, I report a post, and that post is blocked.

So, in the PHP & Mysql backend, I would do something like.

  1. reported_posts = MySQL GROUP_CONCAT(reported_post_id) and fetch all my reported posts, store it in some cache like memcached or redis. This will give me a response with comma separated post_ids like 123, 234, 45
  2. Fetch all homepage_posts which are NOT IN (reported_posts). This will give us all the post_ids that needs to be in the homepage other than the posts, 123, 234 and 45, as I have used NOT IN.

The issue here is that, as time goes by, the reported_posts will keep on increasing(lets assume it increases 1000 ids). At that time, the NOT IN (reported_posts) clause will take a huge input. Will this effect the performance of the query? What is an alternative solution to this?

I hope I could convey my doubt clearly, please let me know if it needs more clarification, I would edit as such. Thank you.

EDIT

The Reported post is not to be considered Globally, i.e. If I report the post, it should be Hidden only for me, and not for anyone else. So, it's also dependent on the account_id as well.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    dslf46995 dslf46995 2016-04-26 12:38

    Assuming that reported_posts contains a list of user-specific blacklisted posts, it would be much better to do an exclusive left join and let the database handle everything:

    SELECT *
    FROM homepage_posts hp
    LEFT JOIN
       reported_posts rp 
       ON hp.id = rp.post_id       
          AND rp.account_id = 123
    WHERE 
       rp.id IS NULL
    
    点赞 评论 复制链接分享
  • dsyak22488 dsyak22488 2016-04-26 12:44

    In mysql "IN" operator works fine if the column is indexed. If that column is not indexed then it impacts performance.

    点赞 评论 复制链接分享

相关推荐