MySQL GROUP BY performance issue

Question

This is the query I'm performing (without some Joins that are not relevant):

SELECT a.*, c.id
FROM a
LEFT OUTER JOIN b ON a.id = b.id_anunciante
LEFT OUTER JOIN c ON c.id = b.id_rubro
GROUP BY a.id

Each row of "a" is linked with 1 to 5 rows in "b".

The problem is that GROUP BY has performance issues (it takes 10x or more using GROUP BY than not using it). I need to retrieve only one row of each member in "a".

How can I make this faster?

edit: I need to be able to filter by a.id AND/OR c.id. The resultset I should be getting is only 1 row per "valid" member of "a", meaning the rows that match the constraints. Rows that don't match the filters shouldn't be returned. In my original query, this would be done this way:

SELECT a.*, c.id
FROM a
LEFT OUTER JOIN b ON a.id = b.id_anunciante
LEFT OUTER JOIN c ON c.id = b.id_rubro
WHERE c.id = 1
OR a.id = 1
GROUP BY a.id

a.id, b.id_anunciante, b.id_rubro, c.id are all indexes.

Answer 1

SELECT  a.*,
        (
        SELECT  c.id
        FROM    b
        JOIN    с
        ON      c.id = b.id_rubro
        WHERE   b.id_anunciante = a.id
        -- add the ORDER BY condition to define which row will be selected.
        LIMIT 1
        )
FROM    a

Create the index on b (id_anunciante) for this to work faster.

Update:

You don't need the OUTER JOINs here.

Rewrite your query as this:

SELECT  a.*, c.id
FROM    a
JOIN    b
ON      b.id_anunciante = a.id
JOIN    c
ON      c.id = b.id_rubro
WHERE   a.id = 1
UNION ALL
SELECT  a.*, 1
FROM    a
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    c
        JOIN    b
        ON      b.id_rubro = c.id
        WHERE   c.id = 1
                AND b.id_anunciante = a.id
        )

Answer 2

Add ORDER BY NULL to avoid the implicit sorting MySQL does when doing a group by.

I suppose you have indexes/PKs on a.id, b.id_anunciante, b.id_rubro and c.id ? I guess you could try adding a composite index on (b.id_anunciante, b.id_rubro) if your mysql version is not able to do an index merge.