Since Oracle announced the InnoDB plugin which provides compressed row formats, I thought I'd do this comparison again.
Here are some more data that I've cobbled together.
I've created a 2M row table with a schema that we use for audit data. It has 47 columns and 17 indexes.
I copied the table into new empty tables using INSERT ... SELECT with each of these engines:
- InnoDB row_format=compressed (from the InnoDB plugin)
- InnoDB row_format=Compact
- InnoDB row_format=Redundant
- MyISAM pack_keys=1
- MyISAM packed with myisampack (NOTE: these tables are readonly)
I am very impressed with how good Falcon is. I am very impressed with InnoDB's compressed row format's space usage.
I don't think it's very reasonable to compare the loading times for the different engines as they all work in different ways, but some were a LOT faster than others. Take this with a pinch of salt:
Fast: MyISAM, Falcon, InnoDB except compressed
Slow: InnoDB compressed, PBXT
InnoDB compressed and PBXT both took well over an hour to complete what the others did in a few minutes. I can't explain this - maybe it's a tuning issue.
In each case, the copy was done in an INSERT ... SELECT which uses a single transaction. I tried to tune the buffers as best as I could (the server has enough ram) but I'm no expert in PBXT and Falcon.