Indexing for queries including count

[Omar Khan]

We are working on an in house solution for analyzing network packets. Hence we are required to handle millions of data (e.g. 1 million or 1000,000 records/day) .

I studied over the way to apply indexing and how it improves the speed. And i did applied multi row indexing to some of my rows, that did increased the speed of page loading.

But if go over to the summary page of my portal, that displays trends over time, summarized data, top data and counts, its way too slow. These page contains queries like,

select count(event_id) as the_count, field1, field2 from table where date='somedate' group by field1 order by the_count;

Explain shows that this command is using where,filesort,temporarytable etc. and i beleive that is just because of ordering table by the count. And i dont know how to effectively apply indexing for such queries and i do need to speed up things for these summary pages specially.

Thanks in Advance