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?