Voting Script, Possibility of Simplifying Database Queries

Question

I have a voting script which stores the post_id and the user_id in a table, to determine whether a particular user has already voted on a post and disallow them in the future.

To do that, I am doing the following 3 queries.

SELECT user_id, post_id from votes_table where postid=? AND user_id=?

If that returns no rows, then:

UPDATE post_table set votecount = votecount-1 where post_id = ?

Then

SELECT votecount from post where post_id=?

To display the new votecount on the web page

Any better way to do this? 3 queries are seriously slowing down the user's voting experience

Edit

• In the votes table, vote_id is a primary key
• In the post table, post_id is a primary key.
• Any other suggestions to speed things up?

Answer 1

You can merge the first two queries:

UPDATE  post
SET     votecount = votecount - 1
WHERE   post_id = ?
        AND post_id NOT IN
        (
        SELECT  post_id
        FROM    votes_table
        WHERE   user_id = ?
        )

You still need to run the third one.

Make sure that you have a unique index or a PRIMARY KEY on votes_table (user_id, post_id) and post (post_id).

Most probably, your current query slowness is caused by absence of these indexes.

Answer 2

Quassnoi's answer is going to pretty inefficient by virtue of pulling every post_id the user has ever voted on into the NOT IN.

UPDATE 
    dbo.Post
SET 
    VoteCount = VoteCount + 1 -- Must be +1 right? Not minus 1?
FROM
    dbo.Post p
WHERE
    NOT EXISTS
        (
        SELECT
            v.PostID
        FROM
            dbo.Votes_Table v
        WHERE
            v.PostID = p.PostID
        AND v.User_ID = ?
        )
WHERE
    p.PostID = ?

IF(@@ROWCOUNT = 1)
    PRINT 'No previous vote, votes incremented'
ELSE
    PRINT 'User has already voted, votes not incremented'

Personally, I'd seriously reconsider maintaining a vote_count on the post table.