Speeding up GROUP BY, SUM and AVG queries
===

The table arg_rec contains 800K rows on my test machine, normally this table will hold over 15M rows. I want to run the following query :

    SELECT STE_ID, PNT_NO, YR, MN, AVG(AVR_WS) AS AVR_WS, SUM(AVR_PW) FROM arg_rec GROUP BY STE_ID, PNT_NO, YR, MN;

This query gives daily average wind speed and total power from wind turbine data. On my test machine this query times out after 10 minutes of execution with a combined index on `STE_ID`, `PNT_NO`, `YR`, `MN`, which is only a subset of the primary key columns. Without the index the query completes after several minutes.

I am running with pretty much a stock MySQL installation, in addition to tweaking the server I would also like to know more about other ways to handling this problem, such as :

1. Is it possible to create a view based on this query and cache the results ?
2. Are there more advanced indexing features to encapsulate the fact that YR, MN, DY, HR, MI, SC correspond to year, month, day etc field of the record timestamp ?
3. Am I better off just duplicating the data using the business layer of my application ?
