MySQL - Choosing appropriate storage engines

MyISAM table-level locking works best if the table is read-intensive (few updates).

Use InnoDB if the table must involve many updates. InnoDB's use of row-level locking and multi-versioning provides good concurrency for a mix of retrievals and updates.

For MyISAM tables, use fixed-length columns (CHAR, BINARY) for best speed. The disadvantage is that the column takes more storage space.

For MyISAM tables, use variable-length columns (VARCHAR, VARBINARY, TEXT, BLOB) for best use of disk space.

MyISAM supports compressed read-only tables.

InnoDB implements storage for both CHAR and VARCHAR in a similar way. In fact, retrieval of CHAR values might be slower because they require more information to be read from disk.

MERGE tables can use a mix of compressed and uncompressed tables. This can be useful for time-based records. For example, if you log records for each year to a different log file, you can use an uncompressed log table for the current year so you can update it, but compressed tables for past years to save space. If you then create a MERGE table from the collection, you can easily run queries that search all tables together.

Only MyISAM engine supports full-text or spatial indexes.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License