suitable indexes for where group-by plus plus order-by

Question

I have query that uses order-by group-by

select count(*),filed2
from table1 where field1>x group by filed2 order by  count(*) desc

what are the best indexes for this query.
sholud I index filed1,field2 seprate or together?

Answer 1

You should create the index with both columns in two different orders

ALTER TABLE table1 ADD INDEX field1_field2_ndx (field1,field2);
ALTER TABLE table1 ADD INDEX field2_field1_ndx (field2,field1);

You should not create individual indexes because making the index with both columns will cause the query to pass through the index only to satisfy the query. It would never need to touch the table.

Even if you made individual indexes, the Query Optimizer would choose the two column index anyway.

Now that you have the two indexes, just trust the Query Optimizer to select the correct index. Based on the query, the EXPLAIN plan would choose the field2_field1_ndx index.