MySQL and PHP performance of multiple queries
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.
Answer
Solution:
Or you could use a single query for the up/down votes:
MySQL will take the boolean true/false results of those
thumb = X
tests, convert to integer0
or1
, and then sum them upAnswer
Solution:
You can combine the first two queries like that:
Since the last query seems to be semantically different, I would keep it separate from the one mentioned here for clarity.