"Scott Marlowe" wrote:
> I second this. Partitioning in time in past reporting databases
> resulted in huge performance improvements for select queries.
Most statements will load data from a single year, but multiple monthes.
I have a integer field containing the year and will use it for
partitionning.
It will also help a lot to remove one year after 4 years of activity.
Actually this same database is used with 2 millions of lines per year
(instead of 30). It is loaded with 3 years and runs quite fast
unpartitionned on a 4 years old single SCSI HD with 2Go of RAM, single core
pentium4.
It runs a LOT faster on a quad xeon 2.83GHz with 8Go of ram and SATA HD,
which is quite common now for dedicated servers.
I tried partitionning on it: it showed no performance gain for such a small
size, but it is an evidence that it will help with 30 millions of
lines/year.
OK, thanks to all your recommandations, I will ask hosters for a RAID10
4x250go SATA.