You need to count number of entries for each of the states, which means every order needs to be accessed. So FullTable scan is expected for this query and you can't get much better (well may be have index scan instead)

If you need such reporting in real-time you need counter table.

Comment

You need to count number of entries for each of the states, which means every order needs to be accessed. So FullTable scan is expected for this query and you can't get much better (well may be have index scan instead)

If you need such reporting in real-time you need counter table.

Thanks for the response Peter. I'm still confused by the EXPLAIN output from MySQL. It makes sense that it would have to go through every line item, but I don't understand why it wouldn't use the index for state on the customers table. Is it just not possible to use an index on a column being used for GROUP BY?