If there is one thing that a DBA or data warehouse architect can
count on, it is that data volumes will increase while budgets
This is why MySQL 5.1 and its partitioning capabilities are so interesting. I’m going to demonstrate how you can build a small/medium-sized data warehouse or data mart (1-10 TB range) on a shoe-string budget.
I decided to convert a relatively large statistics table (750m
rows, 140GB in size in about 10 partitions) on a test machine
from MyISAM to the Archive storage engine. After a long
conversion process, my data, on disk, ended up being about 21GB,
for an impressive compression ratio of 6.7:1.
Prior to MySQL 5.1, one of the drawbacks to the archive storage engine was that you could not index it; however, with partition pruning, you can get yourself a “free” index on a large archive table by splitting it into date-based chunks, whether by …