When to use MyISAM and InnoDB? [duplicate]

by

Read about Storage Engines.

MyISAM:

The MyISAM storage engine in MySQL.

  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. — Mostly no longer true.
  • Full-text indexing. — InnoDB has it now
  • Especially good for read-intensive (select) tables. — Mostly no longer true.
  • Disk footprint is 2x-3x less than InnoDB’s. — As of Version 5.7, this is perhaps the only real advantage of MyISAM.

InnoDB:

The InnoDB storage engine in MySQL.

  • Support for transactions (giving you support for the ACID property).
  • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
  • Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
  • InnoDB is more resistant to table corruption than MyISAM.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future enhancements will be in InnoDB. This was made abundantly clear with the roll out of Version 8.0.

MyISAM Limitations:

  • No foreign keys and cascading deletes/updates
  • No transactional integrity (ACID compliance)
  • No rollback abilities
  • 4,284,867,296 row limit (2^32) — This is old default. The configurable limit (for many versions) has been 2**56 bytes.
  • Maximum of 64 indexes per table

InnoDB Limitations:

  • No full text indexing (Below-5.6 mysql version)
  • Cannot be compressed for fast, read-only (5.5.14 introduced ROW_FORMAT=COMPRESSED)
  • You cannot repair an InnoDB table

For brief understanding read below links:

  1. MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and
    Cons
  2. MySQL Engines: MyISAM vs. InnoDB
  3. What are the main differences between InnoDB and MyISAM?
  4. MyISAM versus InnoDB
  5. What’s the difference between MyISAM and InnoDB?
  6. MySql: MyISAM vs. Inno DB!

More Related Contents:

  1. Difference Between Schema / Database in MySQL
  2. How do I show the schema of a table in a MySQL database?
  3. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  4. Which DB technology is sued to solve the following search issue? [closed]
  5. MyISAM versus InnoDB [closed]
  6. How to select the nth row in a SQL database table?
  7. Compare two MySQL databases [closed]
  8. How should I resolve –secure-file-priv in MySQL?
  9. What does character set and collation mean exactly?
  10. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  11. Storing image in database directly or as base64 data?
  12. Implementation of Levenshtein distance for mysql/fuzzy search?
  13. Does MySQL ignore null values on unique constraints?
  14. How to store arrays in MySQL?
  15. What’s the difference between MyISAM and InnoDB? [duplicate]
  16. Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
  17. In SQL, is it OK for two tables to refer to each other?
  18. MySQL SELECT increment counter
  19. How to get all columns’ names for all the tables in MySQL?
  20. How to increase MySQL connections(max_connections)?
  21. Does mysql have the equivalent of Oracle’s “analytic functions”?
  22. MySql views performance [closed]
  23. MySQL: LAST_INSERT_ID() returns 0
  24. How does MySQL Auto Increment work?
  25. Calculating a Moving Average MySQL?
  26. How to get size of mysql database?
  27. What is the difference between SQL and MySQL? [closed]
  28. mysql Failed to read auto-increment value from storage engine
  29. How to Get True Size of MySQL Database?
  30. MySQL: Computed Column

Leave a Comment