I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly this. However, this engine is not available on my hosting provider.
The only (useful) options available are MyISAM, InnoDB, and CSV. I am pretty sure I should not use CSV for this option, so let's discount that one.
What I'm looking for then is the engine that provides the best alternative, keeping in mind the following:
- Good compression would be very nice. A lot of the data may be very similar between rows (as is the case with log messages)
- I don't need a fast look up. Indexes aren't really necessary. Again, I'm looking to save as much space as possible.
- There needs to be support for lots of rows. Hence the need for compression and small footprint.
- Rows do have a foreign key to another table, but it is not necessary to retain the relationship. Each log entry has a name associated with it that is stored in the other table, which doesn't change.
So based on that, which is better, InnoDB, or MyISAM. I know that the MyISAM engine installed on this server only supports 2^32 rows. If InnoDB has similar support for compression and supports more rows, that would be better (but I don't know if that is the case).