title: Speeding up GROUP BY, SUM and AVG queries
body: <p>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 :</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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 <code>STE_ID</code>, <code>PNT_NO</code>, <code>YR</code>, <code>MN</code>, which is only a subset of the primary key columns. Without the index the query completes after several minutes.</p>
  
  <p>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 :</p>
  
  <ol>
  <li>Is it possible to create a view based on this query and cache the results ?</li>
  <li>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 ?</li>
  <li>Am I better off just duplicating the data using the business layer of my application ?</li>
  </ol>