CODEWITHSUNDEEP
mysql
Competitions
Competitions

Reputation: 37

mysql multiple count in single table with different where clause

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?

Upvotes: 1

Views: 1048

Answers (2)

user359040
user359040

Reputation:

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')

Upvotes: 3

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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

Upvotes: 0

Related Questions