Mysql – Which MySQL storage engine is the most similar to ARCHIVE when that is not available

innodbmyisamMySQLstorage-engine

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

Best Answer

InnoDB supports compression as of MySQL 5.1 (with InnoDB Plugin), or natively in MySQL 5.5. The level of compression really depends on your table content, but I see 75% reduction in size as common.

Reads and writes to compressed tables involve more CPU. InnoDB will support your foreign keys as well as any indexes.

I would say it is a fairly good out-of-the-box solution for your problem.

MyISAM only supports compression on a read-only mode.

Or you might find an altogether NoSQL solution to do the job well for you, given that you don't really need indexes.

I'm a relational geek, so I would first go for InnoDB. Just:

ALTER TABLE my_table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

KEY_BLOCK_SIZE can be 1,4,8,16; Lower numbers will give you better compression. My experience shows that 4 gives good compression. 1 rarely improves much, and consumes a lot more CPU.

Related Question