MySQL Grouping SubQuery Optimization -

i have table of categories, , table of products. products have category_id, , maker_id.

i attempting return table of category names, along binary of whether or not category contains products belong given $maker_id (as defined in php code).

my current method counts how many matching products in each category, i'm assuming there faster way since need yes/no. current code:

select      c.name,     sum(case when p.maker_id = '{$maker_id}' 1 else 0 end) already_used categories c left join products p on p.category_id = c.id group c.id 

i'm reading on using exists, examples i've found using in clause. thanks!

you can try this:

select c.name,count(1) already_used, sum(if(p.status = 'ready', 1, 0)) ready_count  categories c  left join products p  on  (p.category_id = c.id)  p.maker_id = '{$maker_id}' group c.id;