MySQL: Pulling the current user's vote on a query of links.

Question

So I have this query that pulls from my links and votes table and I need one last column of data. My votes table consists of every user's vote, a user can only vote once per link and their vote value is either -1, 0 or 1. There is a user_id foreign key in the votes table and I want to somehow gather the current user's vote. I feel the complexity of the current query might require a second query but I really want to avoid that if possible. I simply need to know what the current logged in user's vote was. A link will never have more than one vote value because a user can only vote on a link once.

A few notes

1. All links start automatically with at least one vote entry by the current user
2. A user that votes on a link then deselects that vote will keep that vote entry with a 0 delta

SQL:

SELECT links.*, (SUM(votes.karma_delta)) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id
ORDER BY (SUM(votes.karma_delta) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC
LIMIT 0, 100

While optimization is great, right now I just want to get the selected links karma_delta by a specified user.

Answer 1

Here is one way you can make this faster: if you need to show the scores for each of the links frequently, and you vote on the links not very frequently, I'd denormalize the data structure in the following way:

• Create a column on the link table called "current score"
• Whenever you make a modification to the votes table, also update the current score
• If you ever worry about the two getting out of sync, run a daemon that overrides the values of the current score with the "aggregation of all votes".

Then, showing the score of each of the links is mega-fast; of course, the cost you're paying here is at the vote time (you're doing two inserts/updates instead of one), as well as some extra complexity.

Answer 2

I'm really not sure of what you're asking, but it sounds like you want to keep the information you're already returning and simply augment it with the sum of votes pertaining to the current user for each link.

If that's the case, then something like this should do it:

SELECT links.*, 
       SUM(votes.karma_delta) AS karma,
       SUM(
           IF(votes.user_id = current_user_id, 
              votes.karma_delta, 
              0)
          ) AS user_vote
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id
ORDER BY (SUM(votes.karma_delta) - 1) / 
         POW(
              TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2, 
              1.5
            ) DESC
LIMIT 0, 100