,

How to choose the best MySQL storage engine

Contents

Choosing the storage engine

If the issue is about:

  • Transactions: InnoDB. If transactions is not used: MyISAM
  • Concurrency: If just need insert and select, MyISAM is good dealing with concurrency. For other concurrency problems, row-level lock tables are better
  • Backups: All are good, but memory tables will be empty for backups
  • Crash recovery: InnoDB or Maria (to be available after version 6)
  • Special features:
    • MyISAM is the only that support full text search
    • If clustered index optimizations are need, InnoDB and solidDB are the only ones that support it

Examples

  1. If inserts and selects are the main use for a table, MyISAM is the best cause deals with thousands of rows per second. But if you generates summary data for reports, it can slow insertion processes because of the table-level lock. 2 things can be done in this case:
  • Generate, or populate, summary tables from time to time, in low load periods.
  • Create 1 table per month, or year, and use Merge engine to summarize it
  1. If table is read more often than written in, MyISAM

Table Conversions

  • All special features will be lost
  • Alter table locks it on a read-level and it might take long time
  • The best option is to dump table and import it to a copy
Retrieved from "http://www.thalesjacobi.com/How_to_choose_the_best_MySQL_storage_engine"