MySQL GROUP BY Performance Error

Question

This is the query that I execute (without some links that don't matter):

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 line "a" is associated with 1 to 5 lines in "b".

The problem is that GROUP BY has performance issues (it takes 10 or more times using GROUP BY rather than using it). I need to get only one line of each element in "a".

How can I do it faster?

edit: I need to be able to filter through a.id AND / OR c.id. The result I should get is only 1 line per "valid" member "a", which means lines that meet the constraints. Rows that do not match filters should not be returned. In my initial request, this will be done as follows:

 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 - 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 an index on b (id_anunciante) to make it work faster.

Update:

You don't need OUTER JOINs .

Rewrite your request as follows:

 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 MySQL implicit sorting when executing the group.

I assume you have indices / PK on a.id, b.id_anunciante, b.id_rubro and c.id? I think you could try adding a compound index to (b.id_anunciante, b.id_rubro) if your version of mysql cannot perform index merging.