mysql multiple count in single table with different where clause

Question

I have three SELECT statements that do COUNTs. Thought it would be more efficient and kinder on mysql to combine these:

$query = $db->query("SELECT count(category) FROM table WHERE cid='290463' and category = 'x'"); 
         $tot_x = $db->result($query, 0);

$query = $db->query("SELECT count(category) FROM table WHERE cid='290463' and category = 'y'"); 
         $tot_y = $db->result($query, 0);

$query = $db->query("SELECT count(category) FROM table WHERE cid='290463' and category = 'z'"); 
         $tot_z = $db->result($query, 0);

into one statement:

SELECT 
    SUM(category='x' AND cid='290463') as tot_x, 
    SUM(category='y' AND cid='290463') as tot_y, 
    SUM(category='z' AND cid='290463') as tot_z 
FROM table

Problem is, the new statement is slower than running the original three separately.

Anyone able to shed light on why the new statement is slower and also recommendations to improve the SELECT to be faster?

score 3 · Accepted Answer · 2013-01-29 13:21:55Z

3

Your original queries were probably able to make use of indexes on your table (based on your where clause), ensuring that only a relatively small proportion of the records on the table were read - instead of the entire table, as in your unified query. Try adding equivalent conditions back into your where clause, like so:

SELECT 
    SUM(category='x') as tot_x, 
    SUM(category='y') as tot_y, 
    SUM(category='z') as tot_z 
FROM table
WHERE cid='290463' and category in ('x', 'y', 'z')

edited Jan 29, 2013 at 13:21

answered Jan 29, 2013 at 13:04

user359040

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

2 Comments

Competitions Over a year ago

Aha! Thanks Mark, indexes are the likely explanation! Will try the new SELECT tomorrow.

user359040 Over a year ago

@MahmoudGamal: Oops, yes! Thanks - I have updated my answer accordingly.

Muhammad Raheel · Accepted Answer · 2013-01-29 13:09:23Z

0

SELECT 
    SUM(IF(category='x',1,0))as tot_x, 
    SUM(IF(category='y',1,0))as tot_y, 
    SUM(IF(category='z',1,0))as tot_z
FROM table
WHERE cid='290463'
GROUP BY category

edited Jan 29, 2013 at 13:09

answered Jan 29, 2013 at 13:03

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Collectives™ on Stack Overflow

mysql multiple count in single table with different where clause

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related