MySQL and PHP performance of multiple queries

812

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.

747

Answer

Solution:

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

{-code-1}

MySQL will take the boolean true/false results of thosethumb = X tests, convert to integer0 or1, and then sum them up

617

Answer

Solution:

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.

People are also looking for solutions to the problem: expressionengine - PHP explode not working on string, fetched from EECMS template

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.