2

I'm asking you for some advice. I have a website where I have videos and users can give them thumbs up and thumbs down. Their saved in a single table. Currently I have three sql queries to get the count of thumbs up, the count of thumbs down and what the logged in user gave (if he did).

And now I'm thinking about making that more performance, but I don't know what is better, since I want to keep the count of queries down.

Method 1) Keep these three queries as they are:

SELECT COUNT(*) as rowcount FROM `videolikes` WHERE `vid` = 'gt6w_RZfs5yx' AND `thumb` = '1' LIMIT 1
SELECT COUNT(*) as rowcount FROM `videolikes` WHERE `vid` = 'gt6w_RZfs5yx' AND `thumb` = '0' LIMIT 1
SELECT * FROM `videolikes` WHERE `vid` = 'gt6w_RZfs5yx' AND `uid` = '1' LIMIT 1

Method 2) Make one query with sub queries (something like that (it doesn't work how it is here)):

SELECT *, (SELECT COUNT(*) as rowcount FROM `videolikes` WHERE `vid` = 'gt6w_RZfs5yx' AND `thumb` = '1' LIMIT 1) as thumbsups, (SELECT COUNT(*) as rowcount FROM `videolikes` WHERE `vid` = 'gt6w_RZfs5yx' AND `thumb` = '0' LIMIT 1) as thumbsdowns FROM `videolikes` WHERE `vid` = 'gt6w_RZfs5yx' AND (`uid` = '1' OR `uid` = NULL)

Method 3) Your own idea, maybe?

Tell me what you think and give some code (if you want).

Best Regards Charlotte

edit: I'll add some more information: vid is the id of the video. Everything about the likes is stored in this table, referenced to the video with the VideoID (vid). uid is the UserID who gave the like (or dislike). That means there isn't only the likes and dislikes of one video in this table. To know which like and dislike is for which video, the like will be stored with the videoid.

Improve this question
4
  • Perhaps use a UNION so that it can all be run at one time. You could also create a stored procedure that gets called once and returns all of the data. Commented Jul 31, 2014 at 15:24
  • 1
    I'd recommend to keep the historical videolikes table, and store that information, but to also store the counts with the video record. Commented Jul 31, 2014 at 15:25
  • +1 to what @Sonny said. If you are concerned about performance, simply keep a counter col in your video table. Then in your function that inserts video likes, increment the counter in the video table. Commented Jul 31, 2014 at 19:09
  • Hmm, it's amazing: I found numerous answers to this question after searching :< Commented Aug 2, 2014 at 6:27

2 Answers 2

Reset to default
6

Or you could use a single query for the up/down votes:

SELECT SUM(thumb = 1) AS upvote, SUM(thumb = 0) AS downvote, ....

MySQL will take the boolean true/false results of those thumb = X tests, convert to integer 0 or 1, and then sum them up

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That wouldn't work and will only result in 1 upvote or 1 downvote since the WHERE clausel includes the userid and videoid and only one row will be returned and that's his vote. I can be wrong, but that would mean you'd have to construct the full query so I can understand what you want to do.
yes, you'd have to run your uid query separately. the above query would be run on the entire table with a simple where vid=XXX filter.
3

You can combine the first two queries like that:

SELECT
  SUM(IF(`thumb` = 1, 1, 0)) AS rowcountthumb0,
  SUM(IF(`thumb` = 0, 1, 0)) AS rowcountthumb1
FROM `videolikes` WHERE `vid` = 'gt6w_RZfs5yx'

Since the last query seems to be semantically different, I would keep it separate from the one mentioned here for clarity.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.