[mysql] What's the difference between MyISAM and InnoDB?

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".

If you need the database to enforce foreign key constraints, or you need the database to 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) then you would choose the InnoDB engine, since these features are absent from 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.

So, in choosing between InnoDB and MyISAM, the first step is in determining if you 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.

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to innodb

How can I rebuild indexes and update stats in MySQL innoDB? Database corruption with MariaDB : Table doesn't exist in engine How to regex in a MySQL query mysqldump exports only one table TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes What's the difference between MyISAM and InnoDB? Why is MySQL InnoDB insert so slow? How to debug Lock wait timeout exceeded on MySQL? How to change value for innodb_buffer_pool_size in MySQL on Mac OS? #1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

Examples related to myisam

What's the difference between MyISAM and InnoDB? How to test an SQL Update statement before running it? How to properly create composite primary keys - MYSQL Is there a REAL performance difference between INT and VARCHAR primary keys? How can I check MySQL engine type for a specific table? MyISAM versus InnoDB