What are the main differences between InnoDB and MyISAM?

  • What are the main differences between InnoDB and MyISAM?

    If you want a database engine, use InnoDB. You can't compare the two.

    While many of the answers below are correct, they don't boil things down clearly, IMHO. This site does, and the main point: InnoDB is row-level locking, MyISAM is table-level locking. This means, generally speaking, MyISAM will be better for OLAP (analytics, mostly reads) and InnoDB will be better for OLTP (transactions, mostly writes, or at least many writes).

  • First major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn't have FULLTEXT search indexes until v5.6, as does MyISAM. InnoDB also implements transactions, foreign keys and relationship constraints while MyISAM does not.

    The list can go a bit further. Yet, they both have their unique advantages in their favor and disadvantages against each other. Each of them is more suitable in some scenarios than the other.

    So to summarize (TL;DR):

    • InnoDB has row-level locking, MyISAM can only do full table-level locking.
    • InnoDB has better crash recovery.
    • MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
    • InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.

    dear sir, so ultimately what shall one use? MyISAM or InnoDB ? am totally confused...my website is using mysql and i need to decide this.

    depends on the application, write down a list with the features you'll need ( eg. fulltext search, foreign keys ... ) and try to decide on one ( try to rate each feature and then count the score ). you wont be able to have them all but it's up to you to decide witch feature is needed the most .

    I edited his post for clarification.

    @MathiasLykkegaardLorenzen thanks, that's one of the reasons we like stackexchange

    as of `version 5.6.4` InnoDB supports `FULLTEXT` search. https://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html

    InnoDB doesn't remember auto increment values (it forgets them on restart).

    @sqlchild, If you webserver absorbs data from the client, better use InnoDB, the foreign key feature is essential to keep your database in a consistent state.

  • Another major difference not as yet mentioned is how caching for each storage engine is done.

    MYISAM

    The main mechanism used is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following query:

    SELECT CONCAT(ROUND(KBS/POWER(1024,
    IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
    SUBSTR(' KMG',IF(PowerOf1024<0,0,
    IF(PowerOf1024>3,0,PowerOf1024))+1,1))
    recommended_key_buffer_size FROM
    (SELECT LEAST(POWER(2,32),KBS1) KBS
    FROM (SELECT SUM(index_length) KBS1
    FROM information_schema.tables
    WHERE engine='MyISAM' AND
    table_schema NOT IN ('information_schema','mysql')) AA ) A,
    (SELECT 2 PowerOf1024) B;
    

    This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.

    InnoDB

    The main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed. To size your InnoDB Buffer Pool, run the following query:

    SELECT CONCAT(ROUND(KBS/POWER(1024,
    IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
    SUBSTR(' KMG',IF(PowerOf1024<0,0,
    IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
    FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
    WHERE engine='InnoDB') A,
    (SELECT 2 PowerOf1024) B;
    

    This will give the Recommended Setting for the size of the InnoDB Buffer Pool (innodb_buffer_pool_size) given your current data set.

    Don't forget to resize the InnoDB Log Files (ib_logfile0 and ib_logfile1). MySQL Source Code places a cap of the combined sizes of all InnoDB Log Files must be < 4G (4096M). For the sake of simplicity, given just two log files, here is how you can size them:

    • Step 1) Add innodb_log_file_size=NNN to /etc/my.cnf (NNN should be 25% of innodb_buffer_pool_size or 2047M, whichever is smaller)
    • Step 2) service mysql stop
    • Step 3) rm /var/log/mysql/ib_logfile[01]
    • Step 4) service mysql start (ib_logfile0 and ib_logfile1 are recreated)

    CAVEAT

    At the End of both queries is a an Inline Query (SELECT 2 PowerOf1024) B

    • (SELECT 0 PowerOf1024) gives the Setting in Bytes
    • (SELECT 1 PowerOf1024) gives the Setting in Kilobytes
    • (SELECT 2 PowerOf1024) gives the Setting in Megabytes
    • (SELECT 3 PowerOf1024) gives the Setting in Gigabytes
    • No powers less that 0 or greater than 3 is accepted

    EPILOGUE

    There is no substitute for common sense. If you have limited memory, a mixture of storage engines, or a combination thereof, you will have to adjust for different scenarios.

    • If you have 2GB RAM and 16GB of InnoDB, allocate 512M as innodb_buffer_pool.
    • If you have 2GB RAM and 4GB of MyISAM Indexes, allocate 512M as key_buffer_size.
    • If you have 2GB RAM and 4GB of MyISAM Indexes and 16GB InnoDB, allocate 512M as key_buffer_size and 512M as innodb_buffer_pool_size.

    Possible scenarios are endless !!!

    Remember, whatever you allocate for, leave enough RAM for DB Connections and the Operating System.

    Those are bad formulas!

    (oops - keep forgetting can't have paragraphs)... I'll add an "answer".

    Rolando's formulas for cache sizes are not practical. -- Powers of 2 are not needed. -- 4GB on a 32-bit OS is impossible -- Etc. Here's my rundown on what to set them to: http://mysql.rjweb.org/doc.php/memory (It addresses various other settings that affect memory usage.)

    @Rick : The powers of 2 were meant to display the answers in different units. Doing (SELECT 2 PowerOfTwo) Sets the Display of the Answer in MB. Doing (SELECT 3 PowerOfTwo) Sets the Display in GB. (SELECT 1 PowerOfTwo) Displays in KB. (SELECT 0 PowerOfTwo) Displays in Bytes. That's what the (SELECT 2 PowerOfTwo) does. So it is needed to DISPLAY ONLY, not impose any assumed values in the architecture.

    @Rick : The MySQL Website Documentation says 4GB Max for 32-Bit OS : The maximum permissible setting for key_buffer_size is 4GB on 32-bit platforms. Values larger than 4GB are permitted for 64-bit platforms. The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less.

    @Rick : That's what this URL says (http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size), right from the MySQL Documentation. If the documentation is wrong, please inform MySQL (eh Oracle).

    @Rick : Even your URL (http://mysql.rjweb.org/doc.php/memory) clearly says : First, the OS (and the hardware?) may conspire to not let you use all 4GB, if that is what you have. If you have more than 4GB of RAM, the excess beyond 4GB is _totally_ inaccessable and unusable. This is exactly what 4GB is correct from MySQL's documentation and your own URL that you quoted.

    @Rick : You know what ? I will actually give you a +1 for two very big reasons. 1) Your URL confirms my answer was correct in that 4GB is the biggest number to assign to the key_buffer_size. 2) Your answer, with your URL, makes sense for machines very low memory. I'll give credit where credit is due.

    @Rick : I don't mean to be verbose, but if you had read my final note from my answer, you would have seen that I clearly state using 512MB for innodb_buffer_pool_size and key_buffer_size. I do try to conservative with low memory environmennts. I am fully aware that formulas like mine are not absolute.

    I actually gave @Rick a +1 for his answer with regard to low memory environments. My answer, even using his URL from his answer, has been confirmed as correct. From here I leave it to the DBA.SE community to judge all answers in its full context. Thank You !!!

  • InnoDB offers:

    • ACID transactions
    • row-level locking
    • foreign key constraints
    • automatic crash recovery
    • table compression (read/write)
    • spatial data types (no spatial indexes)

    In InnoDB all data in a row except for TEXT and BLOB can occupy 8,000 bytes at most. Full text indexing is not available in InnoDB until MySQL 5.6 (Feb 2013). In InnoDB the COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) execute slower than in MyISAM because the row count is not stored internally. InnoDB stores both data and indexes in one file. InnoDB uses a buffer pool to cache both data and indexes.

    MyISAM offers:

    • fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
    • full text indexing (update: supported in InnoDB from MySQL 5.6)
    • smaller disk footprint
    • very high table compression (read only)
    • spatial data types and indexes (R-tree) (update: supported in InnoDB from MySQL 5.7)

    MyISAM has table-level locking, but no row-level locking. No transactions. No automatic crash recovery, but it does offer repair table functionality. No foreign key constraints. MyISAM tables are generally more compact in size on disk when compared to InnoDB tables. MyISAM tables could be further highly reduced in size by compressing with myisampack if needed, but become read-only. MyISAM stores indexes in one file and data in another. MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.

    Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.

    I read your answer and compared it with the others already here. Yours is the only one to mention BLOBs. They are usually taken for granted. Yours is also the only one mentioning myisampack, one of the unsung heroes of fast-readable MyISAM tables. Yours is a +1 today !!!

    Example would be a compressed read-only table where you have infrequent updates to it by fully replacing the table.

  • A bit late to the game...but here's a quite comprehensive post I wrote a few months back, detailing the major differences between MYISAM and InnoDB. Grab a cuppa (and maybe a biscuit), and enjoy.


    The major difference between MyISAM and InnoDB is in referential integrity and transactions. There are also other difference such as locking, rollbacks, and full-text searches.

    Referential Integrity

    Referential integrity ensures that relationships between tables remains consistent. More specifically, this means when a table (e.g. Listings) has a foreign key (e.g. Product ID) pointing to a different table (e.g. Products), when updates or deletes occur to the pointed-to table, these changes are cascaded to the linking table. In our example, if a product is renamed, the linking table’s foreign keys will also update; if a product is deleted from the ‘Products’ table, any listings which point to the deleted entry will also be deleted. Furthermore, any new listing must have that foreign key pointing to a valid, existing entry.

    InnoDB is a relational DBMS (RDBMS) and thus has referential integrity, while MyISAM does not.

    Transactions & Atomicity

    Data in a table is managed using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE and DELETE. A transaction group two or more DML statements together into a single unit of work, so either the entire unit is applied, or none of it is.

    MyISAM do not support transactions whereas InnoDB does.

    If an operation is interrupted while using a MyISAM table, the operation is aborted immediately, and the rows (or even data within each row) that are affected remains affected, even if the operation did not go to completion.

    If an operation is interrupted while using an InnoDB table, because it using transactions, which has atomicity, any transaction which did not go to completion will not take effect, since no commit is made.

    Table-locking vs Row-locking

    When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.

    When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

    This feature in InnoDB is known as concurrency. As great as concurrency is, there is a major drawback that applies to a select range of tables, in that there is an overhead in switching between kernel threads, and you should set a limit on the kernel threads to prevent the server coming to a halt.

    Transactions & Rollbacks

    When you run an operation in MyISAM, the changes are set; in InnoDB, those changes can be rolled back. The most common commands used to control transactions are COMMIT, ROLLBACK and SAVEPOINT. 1. COMMIT - you can write multiple DML operations, but the changes will only be saved when a COMMIT is made 2. ROLLBACK - you can discard any operations that have not yet been committed yet 3. SAVEPOINT - sets a point in the list of operations to which a ROLLBACK operation can rollback to

    Reliability

    MyISAM offers no data integrity - Hardware failures, unclean shutdowns and canceled operations can cause the data to become corrupt. This would require full repair or rebuilds of the indexes and tables.

    InnoDB, on the other hand, uses a transactional log, a double-write buffer and automatic checksumming and validation to prevent corruption. Before InnoDB makes any changes, it records the data before the transactions into a system tablespace file called ibdata1. If there is a crash, InnoDB would autorecover through the replay of those logs.

    FULLTEXT Indexing

    InnoDB does not support FULLTEXT indexing until MySQL version 5.6.4. As of the writing of this post, many shared hosting providers’ MySQL version is still below 5.6.4, which means FULLTEXT indexing is not supported for InnoDB tables.

    However, this is not a valid reason to use MyISAM. It’s best to change to a hosting provider that supports up-to-date versions of MySQL. Not that a MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.

    Conclusion

    In conclusion, InnoDB should be your default storage engine of choice. Choose MyISAM or other data types when they serve a specific need.

    Thanks, really informative and clear summary.

  • One more thing: you can backup InnoDB tables just by taking a snapshot of the filesystem. Backing up MyISAM requires using mysqldump and is not guaranteed to be consistent (e.g. if you insert into a parent and a child table, you might find only the child table's row in your backup).

    Basically, if you have another copy of the data and are only caching it in MySQL e.g. to allow a standard means of accessing it from a PHP website, then MyISAM is fine (i.e. it's better than a flat CSV file or a logfile for querying and concurrent access). If the database is the actual "master copy" of the data, if you are doing INSERT and UPDATE using real data from users, then it is foolish to use anything other than InnoDB, at any sort of scale MyISAM is unreliable and hard to manage, you'll be doing myisamchk half the time, negating any performance gains...

    (My personal experience: a 2 terabyte DB in MyISAM).

  • In my experience, the most significant difference is the way each engine handles locking. InnoDB uses row locking while MyISAM uses table locking. As a rule of thumb, I use InnoDB for write heavy tables and MyISAM for read heavy tables.

    Other important differences include:

    1. InnoDB support transactions and foreign keys. MyISAM does not.
    2. MyISAM uses full text indexing.
    3. MyISAM does a poor job of enforcing data integrity.

    Out of date -- InnoDB now has `FULLTEXT` and `SPATIAL`. InnoDB is good for _both_ read- and write-heavy loads.

  • I tend to view MyISAM as the 'default' table choice for MySQL, so I'll point out the differences for most users of InnoDB

    • Row level-locking
    • Foreign key enforcement
    • Transaction support
    • Performance hit on high-use systems

    except the latest MySQL release no longer uses MyISAM as the default engine. In 5.5 they changed the default to InnoDB :). And I would disagree with the generalization that InnoDB in general just gets a 'performance hit'. Well designed InnoDB tables with proper indexing and well configured memory settings can make an InnoDB table perform as well as the same schema in MyISAM

    In many "high-use" situations InnoDB actually performs way better than MyISAM. MyISAM is a specific tool for a specific problem, while InnoDB will serve you better in the majority of situations (hence why the MySQL team made it the default engine). It's because MyISAM was the only engine for a long time that the MySQL community grew into the habit of using MyISAM by default, even after InnoDB matured.

    FULLTEXT search for InnoDB was added partway through the MySQL 5.6 development cycle. The URL cited now covers InnoDB also.

  • MYISAM

    MYISAM provides table level locking , FULLTEXT searching. MYISAM has the most flexible AUTO_INCREMENTED column handling off all the storage engines. MYISAM does not support transactions.

    INNODB

    INNODB is transaction safe storage engine. INNODB has commit , rollback and crash-recovery capabilities. INNODB supports foreign key referential integrity.

  • Includes MySQL 5.6 changes

    INNODB STORAGE ENGINE:

    • It provides full ACID (atomicity, consistency, isolation, durability) compliance. Multi-versioning is used to isolate transactions from one another.
    • InnoDB provides auto-recovery after a crash of the MySQL server or the host on which the server runs.
    • InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.
    • MySQL 5.6 builds on the platform of InnoDB fully integrated as the default storage engine
    • Persistent Optimizer Stats: Provides improved accuracy of InnoDB index statistics, and consistency across MySQL restarts.
    • Pruning the InnoDB table cache: To ease the memory load on systems with huge numbers of tables, InnoDB now frees up the memory associated with an opened table. An LRU algorithm selects tables that have gone the longest without being accessed.
    • Supports Full-text search: A special kind of index, the FULLTEXT index, helps InnoDB deal with queries and DML operations involving text-based columns and the words they contain. These indexes are physically represented as entire InnoDB tables.
    • InnoDB seems to be way faster on Full-Text Search than MyISAM

    So, there is no point in using MyISAM Engine if you are already upgraded to 5.6, if not then don't wait for upgrading to MySQL 5.6.

    InnoDB VS MyISAM performance using MySQL 5.6

  • MyISAM

    MyISAM is a storage engine for MySQL. Before MySQL 5.5 it was the default storage engine for MySQL. It is based on the older ISAM storage engine. MyISAM is optimized for environments with heavy read operations, and few writes, or none at all. The reason MyISAM allows for fast reads is the structure of its indexes: each entry points to a record in the data file, and the pointer is offset from the beginning of the file. This way records can be quickly read, especially when the format is FIXED. Thus, the rows are of constant length. A typical area in which one could prefer MyISAM is data warehouse, because it involves queries on very big tables, and the update of such tables is done when the database is not in use (usually by night). Inserts are easy too, because new rows are appended to the end of the data file. However, delete and update operations are more problematic: deletes must leave an empty space, or the rows' offsets would change; the same goes for updates, as the length of the rows becomes shorter; if the update makes the row longer, the row is fragmented. To defragment rows and claim empty space, the OPTIMIZE TABLE command must be executed. Because of this simple mechanism, usually MyISAM index statistics are quite accurate. Other major drawbacks of MyISAM are the absence of transactions support and foreign keys.

    InnoDB

    InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). It implements both SQL and XA transactions, tablespaces, FULLTEXT indexes, and spatial operations following the OpenGIS standard. It is included as standard in most binaries distributed by MySQL AB, the exception being some OEM versions. The software is dual licensed by the Oracle Corporation; it is distributed under the GNU General Public License, but can also be licensed to parties wishing to combine InnoDB in proprietary software.

    Forks

    MariaDB has a storage engine called Aria, which is described as a "crash-safe alternative to MyISAM". MariaDB and Percona Server use a fork of InnoDB called XtraDB by default. XtraDB is maintained by Percona. Oracle InnoDB's changes are regularly imported into XtraDB, and some bug fixes and extra features are added.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM