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

  • Just out of curiosity: what do you want to use this for?
    – dezso
    Dec 11, 2012 at 22:52
  • Session event tracking. Whenever a user performs an action that is worthy of knowing about, an log of the event is inserted as a row in the table.
    – Ed Marty
    Dec 11, 2012 at 23:19

1 Answer 1


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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.