effective retrieve for a voting system in PHP and MySQL

Question

I have a system where registered users can vote up/vote down comments for a picture. It's very similar to Stack Overflow's voting system.

I store the votes in a table with values as such:

vote_id | vote_comment_id | vote_user_id | vote_date | vote_type 

Now I have a few questions concerning the speed and efficiency for the following:

PROBLEM: Once a user opens the picture page with comments, I need if that user has already voted UP/DOWN on a comment to show it like; "you voted up" or "you voted down" next to the comment (in Stack Overflow the vote image is highlighted).

MY POSSIBLE SOLUTION: Right now when I open a picture page I loop through each comment, and I loop through my table of votes as well and check if a user has voted and show the status (I compare the vote_user_id with the user's session).

How efficient is this? Does anyone have a better approach to tackle this kind of problem?

Answer 1

You are looping through the table of votes? Are you reading the entire database into memory and then looping through it?

Have you tried querying the database only for the relevant data?

SELECT vote_comment_id, vote_type
FROM vote
WHERE vote_user_id = 34513
  AND vote_comment_id IN (3443145, 3443256, 3443983)

Answer 2

you don't mention which database you're using but i assume some SQL variant.

so, instead of looping through the entire table of votes, you can do something like

select vote_type from vote_table where vote_comment_id = $commentId and vote_user_id = $userId

or even better, when you're retrieving the actual comments you can do a left join like so

select c.*, v.vote_type from comments c left join (select * from votes where vote_user_id = $userId) v on v.vote_comment_id = c.comment_id

then check if vote_type is null, up, or down in your display loop. this might be less efficient if you have 1000 comments and only show 10 at a time though, in which case the first method should help.

[edited after comment above about vote_type column]

Answer 3

Try to avoid using subselect specially if you are retrieving large number of rows.

select c.*, v.vote_type 
from comments c 
left join vote v 
on v.vote_comment_id = c.comment_id
and v.vote_user_id = $userId

Using CASE statement to display/hide vote_type.

select c.*, CASE v.vote_user_id WHEN $userId
THEN v.vote_type /*compare vote_user_id with the user's session*/
ELSE null END AS 'votetype' /*hide vote_type */
from comments c 
left join vote v 
on v.vote_comment_id = c.comment_id