MySQL: Return grouped fields where the group is not empty, efficiently

Question

In one statement I'm trying to group rows of one table by joining to another table. I want to only get grouped rows where their grouped result is not empty.

Ex. Items and Categories

SELECT Category.id
FROM Item, Category
WHERE Category.id = Item.categoryId
GROUP BY Category.id
HAVING COUNT(Item.id) > 0

The above query gives me the results that I want but this is slow, since it has to count all the rows grouped by Category.id.

What's a more effecient way?

I was trying to do a Group By LIMIT to only retrieve one row per group. But my attempts failed horribly. Any idea how I can do this?

Thanks

Answer 1

Try this:

SELECT  item.categoryid
FROM    Item
JOIN    Category
ON      Category.id = Item.categoryId
GROUP BY
        item.categoryid
HAVING  COUNT(*) > 0

This is similar to your original query, but won't do what you want.

If you want to select non-empty categories, do this:

SELECT  category.id
FROM    category
WHERE   id IN
        (
        SELECT  category_id
        FROM    item
        )

For this to work fast, create an index on item (category_id).

Answer 2

What about eliminating the Category table if you don't need it?

SELECT Item.categoryId 
FROM Item
GROUP BY Item.categoryId

I'm not sure you even need the HAVING clause since if there is no item in a category it won't create a group.

Answer 3

I think this is functionally equivalent (returns every category that has at least one item), and should be much faster.

SELECT 
  c.id
FROM 
  Category c
WHERE
  EXISTS (
    select 1 from Item i where i.categoryid = c.categoryID
  )

Answer 4

I think, and this is just my opinion, that the correct approach IS counting all the stuff. Maybe the problem is in another place.

This is what I use for counting and it works pretty fast, even with a lot of data.

SELECT categoryid, COUNT(*) FROM Item GROUP By categoryid

It will give you a hash with all the items by category. But it will NOT include empty categories.

Then, for retrieveng category information do like this:

SELECT category.* FROM category
INNER JOIN (SELECT categoryid, COUNT(*) AS n FROM Item GROUP By categoryid) AS item
ON category.id = item.categoryid