Should I always prefer MySQL InnoDB over MyISAM?

by

MyISAM is transactionless and heap-organized. The records are identified by the row offset in the table and the indexes store this offset as a row pointer.

InnoDB supports transactions and is index-organized. The records are identified by the value of the PRIMARY KEY (or a hidden internal column is there is no PRIMARY KEY defined) and are stored in a B-Tree. The secondary indexes store the value of the PRIMARY KEY as a row pointer.

Queries that involve full table scans or secondary index lookups are usually faster on MyISAM tables.

Queries that involve PRIMARY KEY seeks are usually faster on InnoDB tables.

MyISAM tables store the number of records in the table in the table’s metadata, that’s why the queries like this:

SELECT  COUNT(*)
FROM    myisamtable

are instant.

MyISAM tables are completely locked on the DML operations (with several exceptions).

InnoDB tables lock individual records and index gaps, however these are the records and the gaps that are scanned, not only those matched by the WHERE condition. This can lead to the records being locked despite the fact they don’t match.

InnoDB tables support referential integrity (FOREIGN KEYs) . MyISAM tables don’t.

There are several scenarios that can show benefits of both engines.

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. Is there a REAL performance difference between INT and VARCHAR primary keys?
  3. What’s the difference between MyISAM and InnoDB? [duplicate]
  4. MySql: MyISAM vs. Inno DB! [closed]
  5. How can I check MySQL engine type for a specific table?
  6. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  7. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  8. What does character set and collation mean exactly?
  9. MySQL AUTO_INCREMENT does not ROLLBACK
  10. 1114 (HY000): The table is full
  11. Restore table structure from frm and ibd files
  12. MySQL – Conditional Foreign Key Constraints
  13. MySQL stored procedures use them or not to use them
  14. In SQL, is it OK for two tables to refer to each other?
  15. UUID performance in MySQL?
  16. Remove Primary Key in MySQL
  17. DOs and DONTs for Indexes [closed]
  18. Is there any performance gain in indexing a boolean field?
  19. MySQL – Supertype/Subtype design
  20. Why does MySQL autoincrement increase on failed inserts?
  21. MySQL InnoDB: autoincrement non-primary key
  22. How do I rename a MySQL database (change schema name)?
  23. Strings as Primary Keys in MYSQL Database [closed]
  24. MySQL Integer vs DateTime index
  25. How do I lock on an InnoDB row that doesn’t exist yet?
  26. Defining Composite Key with Auto Increment in MySQL
  27. MySQL DROP all tables, ignoring foreign keys
  28. “Unknown column in ‘field list'”, but column does exist
  29. MySQL Relationships
  30. How can I rebuild indexes and update stats in MySQL innoDB?

Leave a Comment