mysql group by performance tuning

Question

select field1,count(*) from table where $condition group by field1
select field2,count(*) from table where $condition group by field2

Basically that's what I'm doing the job now,is there a way to optimize the performance so that MySQL doesn't need to search two times to group by for the where clause?

Answer 1

No magic bullet here... the key upon which the aggregation takes place is distinct, so SQL needs to iterated over two different lists. To make this a bit more evident, ask yourself how you would like the data returned: all field1 first, then all field 2, or intertwined, or possibly "pivoted" (but how?..)

To avoid an extra "trip" to the server we could get these two results set returned together, or we could even group the two, using UNION ALL (and being careful to add a prefix column to to know what is what), but this latter solution would end up taxing the server a bit more if anything.

Answer 2

If the table is large and $condition is 'rare', it might help to create a temporary table in memory. This way, you group it twice but filter it only once.

CREATE TEMPORARY TABLE temp ENGINE=MEMORY
 select field1,field2 from table where $condition;

select field1,count(*) from temp group by field1;
select field1,count(*) from temp group by field2;