duanruanxian5028 2018-09-03 05:18
浏览 39
已采纳

优化SQL查询

I faced interesting problem in very simple case. I have posts and users table in MySQL database. Posts can be liked by users. So there is separate table called likes that has columns: user_id, post_id.

When user taps on like button in app, request to php script is executed. Script is checking if there is row in a table where post_id and user_id matches information from request. user can’t has more than 1 like to a post, and if I press like on already liked post, previous like should disappear

I use 2 queries now: 1) check like to exist in table 2) If record does not exist - I am adding it, and if exists, I delete it.

Now I see that there are double likes for same post from same user in my table. It seems that two requests from single user could be executed almost instantly.

How can I optimize it - so it would be impossible to add two likes to one post from user? I think that I need to run single query instead but what should it be?

  • 写回答

3条回答 默认 最新

  • douye2111 2018-09-03 05:37
    关注

    Obviously your insert/update part is fired multiple times. You should investigate why this is happening and then prevent this if possible.

    Because of multiple calls, the DBMS is being asked multiple times whether the entry exists. The answer is no for all calls. Then multiple inserts are fired and you get duplicates. So on the SQL side there are two things getting wrong:

    1. Why is it even possible to insert multiple records for the same post and user? A primary or unique key should be provided to make this impossible.

    I consider this obligatory for your datamodel.

    1. The sequence of actions is wrong. A possible solution would be an insert first (one would succeed, the others would fail - the above mentioned key provided of course!), and on failure an update would be issued. MySQL, however, can even do this in one step:

    with ON DUPLICATE KEY:

    insert into likes (post_id, user_id, like_date)
     values (@post_id, @user_id, current_date)
     on duplicate key update like_date = current_date;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题