If you often do range queries on date, consider partitioning your table by date (something like 1 partition per month).
Of course, if you also often do range queries on something other than date, and uncorrelated, forget it.
If you pick your partition to line up with your queries than you can probably do away with the date index. Even if it doesn't always line up perfectly its worth considering.
If you make a lot of big aggregate queries, consider materialized views :
Like "how many games player X won this week", etc
- create "helper" tables which contain the query results
- every night, recompute the results taking into account the most recent data
- don't recompute results based on old data that never changes
This is only interesting if the aggregation reduces the data volume by "an appreciable amount". For instance, if you run a supermarket with 1000 distinct products in stock and you sell 100.000 items a day, keeping a cache of "count of product X sold each day" will reduce your data load by about 100 on the query "count of product X sold this month".
This obviously creates some administration overhead. So long as this is manageable for you this is a great solution. You might also want to look at Mondrian at http://mondrian.pentaho.org/ . It takes some tinkering but buys you some neat views into your data and automatically uses those aggregate tables.
Nik Everett
