reusing count with if statement

Question

I want to optimize the following query to avoid multiple count runs.

SELECT product.Id,
    IF((SELECT COUNT(*) 
        FROM productorders 
        WHERE productorders.ProductId = product.Id) > 0,
       (SELECT COUNT(*) FROM productorders WHERE productorders.ProductId = product.Id),
       9999999999
    )
FROM product

can someone suggest a solution for the same

I am using more complex logic in the magic number 9999999999 which I have avoided here for simplicity

Also this query is a small chunk of a much larger query...which i am avoiding for simplicity

The explain result is as below

+ Options id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY product index NULL CategoryId 8 NULL 25 Using index

3

DEPENDENT SUBQUERY productorders ref PRIMARY PRIMARY 8 tabletest.product.Id 1 Using index 2

DEPENDENT SUBQUERY productorders ref PRIMARY PRIMARY 8 tabletest.product.Id 1 Using index

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

I think that initiating the count query result as MySQL variable before the query using it in IF statement or assigning count result to variable inline in query and reusing variable later could be an answer. Some inspiration could be found here: How to store Query Result in variable using mysql

edited May 23 '17 at 10:28

Community

1
1

answered Apr 01 '16 at 08:13

adomas.m

383
2
12

The challenge i am facing here is to mix the IF with the assignment of COUNT to a variable. – appdevfan Apr 01 '16 at 08:17
Ah, understood the problem now. You want a count for EACH product id. Yeah, than my answer won't help. – adomas.m Apr 01 '16 at 08:20

score 0 · Answer 2 · edited May 23 '17 at 11:59

Edit, this works perfectly and gives back 0 when there are no rows:

SELECT product.Id,
       COUNT(productorders.ProductId)
FROM product
LEFT JOIN productorders ON productorders.ProductId = product.Id
GROUP BY productorders.Id

If you want that it returns 99999999 (which i highly discourage you to do) instead of zero, you just add the if statement from sergey and change it to:

SELECT product.Id,
       IF(COUNT(productorders.ProductId)>0, COUNT(productorders.ProductId), 99999999)
FROM product
LEFT JOIN productorders ON productorders.ProductId = product.Id
GROUP BY productorders.Id

Having 2 COUNT functions in your select in this case shouldn't make the query any slower.

Here are the results of explain on a similar query i just made in my own database (maybe bad example as there are not many rows, i suggest you try for yourself on your own database):

Explain without IF:

EXPLAIN SELECT tbl_stam_land.Land_ID,
     COUNT(tbl_naw_adres.Land_ID)
FROM tbl_stam_land
LEFT JOIN tbl_naw_adres ON tbl_naw_adres.Land_ID = tbl_stam_land.Land_ID
GROUP BY tbl_naw_adres.Adres_ID;
/* Affected rows: 0  Gevonden rijen: 2  Waarschuwingen: 0  Duur van 1 query: 0,015 sec. */

Explain with IF:

EXPLAIN SELECT tbl_stam_land.Land_ID,
     IF(COUNT(tbl_naw_adres.Land_ID) > 0, COUNT(tbl_naw_adres.Land_ID), 99999999)
FROM tbl_stam_land
LEFT JOIN tbl_naw_adres ON tbl_naw_adres.Land_ID = tbl_stam_land.Land_ID
GROUP BY tbl_naw_adres.Adres_ID;
/* Affected rows: 0  Gevonden rijen: 2  Waarschuwingen: 0  Duur van 1 query: 0,015 sec. */

the 0,015 seconds is purely from transport delay, it varies between 0,015 and 0,032 for both.

Edit: My bad, in the program i'm using it automatically adds how long a query ran. check this post to find out how to measure how long a query took to execute:

mysql execution time

The check of NULL I need to do is not on productorders.ProductId but on whether the COUNT in productorders is zero — appdevfan, Apr 01 '16 at 08:30
i understand, but with a left join you will always have one row, that's why i thought this might get around that. what i suggest is like sergey says to just take the count and the check whether it's 0 in the php code — Jester, Apr 01 '16 at 08:32
Are you sure if having count done for the same thing twice has no performance impact. I am not expert enough in sql to comprehend that — appdevfan, Apr 01 '16 at 08:49
it shouldn't have a significant one, the aggregating happens once. it might have a really really small impact but like i said the only way you know for sure is just to measure it yourself using "explain". — Jester, Apr 01 '16 at 08:57

Sergey L · Answer 3 · 2016-04-01T08:30:34.657

0

It is better to user query with aggregation, like this

SELECT product.Id, count(productorders.ProductId) 
FROM product left outer join productorders 
on productorders.ProductId=product.Id 
group by product.Id

The logic with 9999999999 is better to implement on the client and it is dangerous to use magic numbers. Implement it explicitly - if the number of orders ==0, do something, else - use the received number.

If you still want to use magic number, try this query:

SELECT product.Id, 
IF(count(productorders.ProductId)>0, 
count(productorders.ProductId), 9999999999)
FROM product 
left outer join productorders on productorders.ProductId=product.Id
group by product.Id

edited Apr 01 '16 at 08:30

answered Apr 01 '16 at 08:25

Sergey L

1,402
1
9
11

this still has 2 counts which i want to avoid – appdevfan Apr 01 '16 at 08:29
2 count's doesn't influence the performance in this case, try using explain in front of your query. – Jester Apr 01 '16 at 08:42
I used explain and it gives the following result:id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY product index NULL CategoryId 8 NULL 25 Using index 3 DEPENDENT SUBQUERY productorders ref PRIMARY PRIMARY 8 tabletest.product.Id 1 Using index 2 DEPENDENT SUBQUERY productorders ref PRIMARY PRIMARY 8 tabletest.product.Id 1 Using index – appdevfan Apr 01 '16 at 08:56

RomanPerekhrest · Accepted Answer · 2016-04-01T10:14:13.100

0

Optimized solution using MySQL User-Defined Variables:

SET @counter := 0;
SELECT product.Id,
       @counter := (SELECT COUNT(*) FROM productorders WHERE productorders.ProductId = product.Id) as cond,
       IF(@counter > 0, @counter, 9999999999) as counter
FROM product

I see that you persistently want to get only product.Id and counter fields - select from subquery:

SELECT product_id, counter(
       SELECT product.Id as product_id,
           @counter := (SELECT COUNT(*) FROM productorders WHERE productorders.ProductId = product.Id) as cond,
           IF(@counter > 0, @counter, 9999999999) as counter
       FROM product) as counter

edited Apr 01 '16 at 10:14

answered Apr 01 '16 at 08:32

RomanPerekhrest

88,541
4
65
105

I get this syntax error with MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check, IF(@counter > 0, @counter, 9999999999) as counter FROM product ' at line 2 – appdevfan Apr 01 '16 at 09:10
@appdevfan, yes, sorry, `check` is not good alias name, change it to any other name. It works well. See my edit – RomanPerekhrest Apr 01 '16 at 09:13
can the 'cond' row be avoided; I need only the counter row also for some reason I get no output with EXPLAIN , I am not sure how to interpret that – appdevfan Apr 01 '16 at 09:28
my approach meets the requirement "to avoid multiple count runs". It works well. You can use custom `counter` field and ignore the `cond` field. You have said: " I need only the counter row" and how about `product.Id` ? – RomanPerekhrest Apr 01 '16 at 09:53
Yes this meets my requirements , I need the productId and the counter column only but now it is outputting an extra column 'cond' that I do not need, I not sure how a custom counter field will help with that – appdevfan Apr 01 '16 at 10:05
you don't understand, `counter` field is your "counter column" – RomanPerekhrest Apr 01 '16 at 10:08
Yes I got it but my question is that I have 3 columns as output,Product.Id, cond and counter, I can continue with the solution by ignoring the cond column in my php logic but is there a way that I do not have the cond column itself as it is unnecessary for me and I have only Product.Id, counter as my sql output columns – appdevfan Apr 01 '16 at 10:17
did you see my update? pay attention for my second query – RomanPerekhrest Apr 01 '16 at 10:24
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT product.Id as product_id, @counter := (SELECT COUNT(*) FROM pr' at line 2 – appdevfan Apr 01 '16 at 10:26
that's your typos. fix them – RomanPerekhrest Apr 01 '16 at 10:29

reusing count with if statement

+ Options id select_type table type possible_keys key key_len ref rows Extra

3

4 Answers4