What’s the difference between MyISAM and InnoDB? [duplicate]

by

The main differences between InnoDB and MyISAM (“with respect to designing a table or database” you asked about) are support for “referential integrity” and “transactions”.

We choose InnoDB if we need the database to enforce foreign key constraints or support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted). These features are not supported by the MyISAM engine.

Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.

In choosing between InnoDB and MyISAM, the first step is to determine if we need the features provided by InnoDB. If not, then MyISAM is up for consideration.

A more detailed discussion of differences is rather impractical (in this forum) absent a more detailed discussion of the problem space… how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

The logical design of the database should be centered around data analysis and user requirements; the choice to use a relational database would come later, and even later would the choice of MySQL as a relational database management system, and then the selection of a storage engine for each table.

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. MySql: MyISAM vs. Inno DB! [closed]
  3. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  4. How do I quickly rename a MySQL database (change schema name)?
  5. Is there a REAL performance difference between INT and VARCHAR primary keys?
  6. How to shrink/purge ibdata1 file in MySQL
  7. MySQL InnoDB foreign key between different databases
  8. How can I check MySQL engine type for a specific table?
  9. How do I rename a MySQL database (change schema name)?
  10. Should I always prefer MySQL InnoDB over MyISAM?
  11. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  12. 1052: Column ‘id’ in field list is ambiguous
  13. Count the occurrences of DISTINCT values
  14. Codeigniter – multiple database connections
  15. 1114 (HY000): The table is full
  16. JOIN queries vs multiple queries
  17. Is the primary key automatically indexed in MySQL?
  18. MySQL stored procedures use them or not to use them
  19. Is there a Profiler equivalent for MySql? [closed]
  20. Cast int to varchar
  21. Import SQL file into mysql
  22. How to skip certain database tables with mysqldump?
  23. “where 1=1” statement [duplicate]
  24. How do you debug MySQL stored procedures?
  25. Meaning of “n:m” and “1:n” in database design
  26. mySQL – Create a New Table Using Data and Columns from Three Tables
  27. MySQL Integer vs DateTime index
  28. What is the best way to do incremental backups in MySQL?
  29. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  30. How to store data with dynamic number of attributes in a database

Leave a Comment