Re: difficulties with time based queries

>
> 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