How can I simplify this query

[fastsol]

I have this query that gathers info from 2 tables but has 3 different where clauses.  I am still learning some of the more advanced aspects of mysql, so I don't really know if/how I could simplify this query string to make it more efficient.  The other thing I don't know is if simplifying it would reduce the actual number of queries ran within itself.  One thing I also don't know is if there are multiple SELECT in a query string, does the query count increase for each of those SELECTS or is it still considered a single query overall.

SELECT `product_reviews`.*,
    (SELECT SUM(`rating`) FROM `product_reviews` WHERE `active`= '1' AND `prod_id`='75') AS `total`,
    (SELECT COUNT(`prod_reviews_helpful`.`help_id`) FROM `prod_reviews_helpful` WHERE `prod_reviews_helpful`.`prod_review_id` = `product_reviews`.`id` AND `prod_reviews_helpful`.`helpful` = '1') AS `total_helpful_yes`,
    (SELECT COUNT(`prod_reviews_helpful`.`help_id`) FROM `prod_reviews_helpful` WHERE `prod_reviews_helpful`.`prod_review_id` = `product_reviews`.`id` AND `prod_reviews_helpful`.`helpful` = '0') AS `total_helpful_no` 
FROM `product_reviews` 
WHERE `active`=TRUE AND `prod_id`='75' 
ORDER BY `id` DESC

Your insight is greatly appreciated!

[fastsol]

Ok well I figured this out once I looked at a previous post I had made about a similar thing.  The only part I couldn't figure out was how to get the total number of ratings in the query itself, so I just used a single line of code afterwards to sum it up.

Here is the revised query string.

SELECT `".PROD_REVIEWS."`.*,
	SUM(CASE WHEN `".PROD_REVIEWS_HELP."`.`helpful` = 1 THEN 1 ELSE 0 END) AS `helpful_yes`,
	SUM(CASE WHEN `".PROD_REVIEWS_HELP."`.`helpful` = 0 THEN 1 ELSE 0 END) AS `helpful_no`
FROM `".PROD_REVIEWS."`
LEFT JOIN `".PROD_REVIEWS_HELP."` ON `".PROD_REVIEWS_HELP."`.`prod_review_id` = `".PROD_REVIEWS."`.`id`
WHERE $where 
GROUP BY `".PROD_REVIEWS."`.`id`
ORDER BY `id` DESC

[fastsol]

Now I need help with another query I am trying to simplify.  This one I have tried many ways and can't figure out.

This is the version that works.

SELECT
(SELECT `name` FROM `quote_responses` WHERE `quote_responses`.`id` = `referrals`.`quote_id` AND `quote_responses`.`purchased` = FALSE) AS `name`,
(SELECT `name` FROM `quote_responses` WHERE `quote_responses`.`id` = `referrals`.`ref_code`) AS `referral_name`,
	`referrals`.`id`	  
FROM `referrals`
WHERE `referrals`.`sent` = FALSE HAVING `name` != '' AND `referral_name` !=''
ORDER BY `referral_name` ASC

This is the modified version that does not work

SELECT `referrals`.`id`,
    CASE WHEN `quote_responses`.`id` = `referrals`.`quote_id` AND `quote_responses`.`purchased` = FALSE THEN `quote_responses`.`name` END AS `name`,
    CASE WHEN `quote_responses`.`id` = `referrals`.`ref_code` THEN `quote_responses`.`name` END AS `referral_name`
FROM `referrals`
INNER JOIN `quote_responses` ON `quote_responses`.`id` = `referrals`.`quote_id` OR `quote_responses`.`id` = `referrals`.`ref_code`
WHERE `referrals`.`sent` = FALSE HAVING `name` != '' AND `referral_name` !=''
ORDER BY `referral_name` ASC

The part that doesn't get data is the 2nd CASE.  If I remove the AND `referral_name` !='' from the WHERE clause I at can see that it's pulling the name for the 1st CASE fine, just nothing for the 2nd.  I've tried GROUP BY and rearranging the order of things, different JOINs, but nothing has worked.  Please don't bother asking if the data is there or if it really references properly between the tables cause the first query has worked perfect from day one, I'm just trying to make things cleaner and faster. 

[Barand]

Don't have your data to test but try this

SELECT 
      qr1.name as name
    , qr2.name as referral_name
    , r.id
FROM
    referrals r
    INNER JOIN quote_responses qr1
        ON qr1.id = r.quote_id 
        AND qr1.purchased = FALSE
        AND qr1.name <> ''
    INNER JOIN quote_responses qr2
        ON qr2.id = r.ref_code
        AND qr2.name <> ''
WHERE r.sent = FALSE
ORDER BY `referral_name`

[fastsol]

Thanks Barand, that worked great!  I've never done stuff with alias tables, still trying to understand how they actually work.  I understand what the <> '' is doing but I don't know the wording to describe it correctly.  Like <= '' would be greater than or equal to nothing.  But without the = sign in there I don't get how to describe it.  Could you also help me understand this question from earlier?

The other thing I don't know is if simplifying it would reduce the actual number of queries ran within itself. One thing I also don't know is if there are multiple SELECT in a query string, does the query count increase for each of those SELECTS or is it still considered a single query overall.

Edited May 15, 2015 by fastsol

[fastsol]

Can you tell me why this query is soooo much slower. I mean like seconds slower too.

SELECT SQL_CALC_FOUND_ROWS 
	`".QUOTE_REQUESTS."`.*,
	qr1.id as already_quoted,
	`".QUOTE_RESPONSES."`.`id` AS `response_id`,
	`".QUOTE_RESPONSES."`.`read_quote`,
	`".QUOTE_RESPONSES."`.`quote_active`,
	`".QUOTE_RESPONSES."`.`viewed_by_txt`,
	`".QUOTE_RESPONSES."`.`viewed_by_email`,
	`".QUOTE_DEPOSITS."`.`id` AS `deposit_id`
FROM `".QUOTE_REQUESTS."`
LEFT JOIN `".QUOTE_RESPONSES."` qr1
        ON qr1.`name` = `".QUOTE_REQUESTS."`.`name` 
  	AND qr1.`vehicle` = `".QUOTE_REQUESTS."`.`vehicle`
							   	
LEFT JOIN  `".QUOTE_RESPONSES."` ON `".QUOTE_RESPONSES."`.`request_id` = `".QUOTE_REQUESTS."`.`id`
LEFT JOIN `".QUOTE_DEPOSITS."` ON `".QUOTE_DEPOSITS."`.`q_id` = `".QUOTE_RESPONSES."`.`id`
GROUP BY `".QUOTE_REQUESTS."`.`id`
ORDER BY `".QUOTE_REQUESTS."`.`id` DESC

Than this original query

SELECT SQL_CALC_FOUND_ROWS 
	`".QUOTE_REQUESTS."`.*,
	(SELECT COUNT(`".QUOTE_RESPONSES."`.`id`) 
		FROM `".QUOTE_RESPONSES."` 
		WHERE `".QUOTE_RESPONSES."`.`name` = `".QUOTE_REQUESTS."`.`name` AND `".QUOTE_RESPONSES."`.`vehicle` = `".QUOTE_REQUESTS."`.`vehicle`) 
	AS `already_quoted`,
	`".QUOTE_RESPONSES."`.`id` AS `response_id`,
	`".QUOTE_RESPONSES."`.`read_quote`,
	`".QUOTE_RESPONSES."`.`quote_active`,
	`".QUOTE_RESPONSES."`.`viewed_by_txt`,
	`".QUOTE_RESPONSES."`.`viewed_by_email`,
	`".QUOTE_DEPOSITS."`.`id` AS `deposit_id`
FROM `".QUOTE_REQUESTS."`   	
LEFT JOIN  `".QUOTE_RESPONSES."` ON `".QUOTE_RESPONSES."`.`request_id` = `".QUOTE_REQUESTS."`.`id`
LEFT JOIN `".QUOTE_DEPOSITS."` ON `".QUOTE_DEPOSITS."`.`q_id` = `".QUOTE_RESPONSES."`.`id`
ORDER BY `".QUOTE_REQUESTS."`.`id` DESC

[fastsol]

One more thing.  Why does SQL_CALC_FOUND_ROWS add 3 queries to the overall query run?  I know that in order to get the number of rows from the SQL_CALC_FOUND_ROWS it runs an additional query, so I would think it only adds 1 to the overall query run, but it's adding 3.  I know this cause I am running a page query test to see how many queries are being ran or a certain page and trying to reduce that to gain load speed.  If I take out the SQL_CALC_FOUND_ROWS the query number is 7, with it in it's 10 and that is the only thing from the query string that I removed, which automatically tells my DB class not to run the additional query to get the total number of rows.

 

For reference this is the part in the DB class that runs the additional query.

if(strpos($this->_sql, 'SQL_CALC_FOUND_ROWS') !== FALSE)
{ $this->_found_rows = $this->_pdo->query('SELECT FOUND_ROWS();')->fetch(PDO::FETCH_COLUMN); }
else{ $this->_found_rows = ''; }

[Barand]

I've never done stuff with alias tables, still trying to understand how they actually work.

Table aliases are normally optional are just a shorthand convenience. In this query we are joining twice to the quote_responses table so we need to able to distinguish between the one joined to quote_id and the one joined to ref_code, hence the aliases are required - think if it as two separate logical tables even though there is only one physical table.

 

"<>" is the same as "!="

 

SELECT

(SELECT `name` FROM `quote_responses` WHERE `quote_responses`.`id` = `referrals`.`quote_id` AND `quote_responses`.`purchased` = FALSE) AS `name`,

(SELECT `name` FROM `quote_responses` WHERE `quote_responses`.`id` = `referrals`.`ref_code`) AS `referral_name`,

    `referrals`.`id`    

FROM `referrals`

WHERE `referrals`.`sent` = FALSE HAVING `name` != '' AND `referral_name` !=''

ORDER BY `referral_name` ASC

 

In the above query each select subquery is called for each record to find the matching records. So if you have 100 results you are calling an extra 200 "dependent" subqueries, a situation which should be avoided by using a more efficient join.

Edited May 15, 2015 by Barand

[fastsol]

Cool, that makes sense now. Any insight into my other recent posts?

[Barand]

 

LEFT JOIN `".QUOTE_RESPONSES."` qr1

ON qr1.`name` = `".QUOTE_REQUESTS."`.`name`

    AND qr1.`vehicle` = `".QUOTE_REQUESTS."`.`vehicle`

I am going to guess there is no index on name or vehicle

[fastsol]

Yep, indexing those fixed it. Thanks!