MySql: MyISAM vs. Inno DB! [closed]

Question

What are the differences between MyISAM and Inno DB types in MySql?

Morphine

possible duplicate of MyISAM versus InnoDB – Weal

Answer 1

The main difference is that InnoDB supports transactions while MyISAM does not.

There are numerous other differences, however the common one's i am aware of are:

• MyISAM has typically been considered faster at searching, but recent InnoDB improvements are removing this difference and improving high concurrency workload performance
• InnoDB support transactions whilst MyISAM does not
• InnoDB supports referential integrity whilst MyISAM does not
• InnoDB handles indexes a bit differently, storing the primary key as part of every index (making indexes take up more room on the disk, but also making a covering index more likely)
• MyISAM does table level locking while InnoDB can do row level locking
• Different memory/buffer/index settings are used in the MySQL configuration files
• InnoDB is typically said to have better crash recovery
• As mentioned in another answer, the data is store on disk differently. I believe InnoDB is configurable in this area and can have one file per table etc. if required

I'm sure a google search or the MySQL site will bring up numerous other differences in more detail.

Causal

Recent improvements in InnoDB performance have eliminated MyISAM's advantages, except in some edge cases. – Freeliving

@Bill - agreed - i have been following these improvements as well. I've updated my answer. – Causal

This discussion caught my attention while I'm revisiting transactionalessness at eBay: infoq.com/interviews/dan-pritchett-ebay-architecture. Particularly interesting is your comment: "Recent improvements in InnoDB performance have eliminated MyISAM's advantages." I want to bleed more scalability out of my web site, mostly as an exercise. MySQL is the long pole in the tent right now, so I'd like to see a side-by-side performance test, Hibernate+InnoDB (which is what I have right now), vs. SQL+MyIsam. May have to write my own. OTOH, table redesign may be the first order of bus. – Neo

@Don - sorry i can't really help with the exact performance tests you are after, but for scaling improvement try taking a look specifically at MySQL 5.4 (in beta still), the InnoDB plugin and XtraDB from Percona. Many of the improvements for scaling on multiple processors have been included in these versions as well as other patches which can be applied. OurDelta also does MySQL builds with many of the patches included. Correct memory tuning is more critical with InnoDB compared to MyISAM and i've found the table design, indexing correctly and then reworking queries is very beneficial as well. – Causal

I made a fresh install of mysql and made a table with ID (key), Name (string) columns. Each insert (through the C interface) took 10ms when the tabel was InnoDB. In MyISAM it took 0.037ms. Writing to a csv with fwritef took 0.007ms. So I think for fast logging MyISAM has superior performance. – Crellen

Answer 2

InnoDB and MyISAM

Features and Performance comparison:

1. InnoDB is newer while MyISAM is older.
2. InnoDB is more complex while MyISAM is simpler.
3. InnoDB is more strict in data integrity while MyISAM is loose.
4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
5. InnoDB has transactions while MyISAM does not.
6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
8. MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.

Advantages of InnoDB

1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
3. No full-text indexing.

Advantages of MyISAM

1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
3. Full-text indexing.
4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

Reference: Comparison InnoDB and MyISAM

You can also check it out here for further details: MyISAM Or InnoDB MySQL engine?

Hope this helps.

Magistracy

+1 That is a great Ocotober 2011 write-up / update. – Appling

@MichaelDurrant: But the content still goes back to sep 2009 – Microgroove

Answer 3

MyISAM supports (non-standard-SQL) fulltext indexing which InnoDB still does not. This is the only reason we ever use MyISAM today.

Bibeau

worth noting that InnoDB now supports full text indexing in mysql >= 5.6 for those coming to this post in 2017 – Barmy

Answer 4

The most important difference between MyISAM and InnoDB is that InnoDB supports transactions and foreign keys. If you need foreign keys and related functionality (for example automatically cascading deletes), you will need to use InnoDB.

InnoDB is slower than MyISAM for most uses, but can perform faster in certain conditions due to a better locking mechanism; MyISAM locks the whole table for reading while inserts/updates are executing. InnoDB can do row-level locking, thus allowing multiple concurrent writes and read on the table.

Berniecebernier

Innodb for the win ! mysqlperformanceblog.com/2007/01/08/… – Disappointed

Answer 5

You can have more information about MyISAM & InnoDB in MySQL Documentation:

http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-overview.html

Dotterel

Answer 6

The major difference is that InnoDB supports transactions, whereas MyISAM doesn't.

Maice

Answer 7

MyISAM and InnoDB also store their data on disk differently. MyISAM uses a data file and an index file for each table, stored in a directory named after the database. InnoDB seems to lump everything together in a file called ibdata1.

Maice

It is possible to force InnoDB to store data separately, even though it's not the default behavior. – Contribution

innodb_file_per_table dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html – Disappointed

Answer 8

NFS support

Unlike MyISAM, InnoDB may have problems on NFS.

From Configuring InnoDB (MySQL version 5.5)

Caution

If reliability is a consideration for your data, do not configure InnoDB to use data files or log files on NFS volumes. Potential problems vary according to OS and version of NFS, and include such issues as lack of protection from conflicting writes, and limitations on maximum file sizes.

Banky

Answer 9

InnoDB Features 1. Provides Full transaction capability with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance.

2. It has row level locking.By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.

3. The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk This enables very fast recovery, and works even on very large data sets.

4. InnoDB supports foreign key constraints

5. InnoDB supports automatic crash recovery
6. InnoDB supports table compression (read/write)

7. InnoDB supports spatial data types (no spatial indexes)

8. Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.

9. Innodb does not have separate index files so they do not have to be opened.

10. Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren't built in optimal order and are fragmented.There is currently no way to defragment InnoDB indexes, as InnoDB can't build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.

11. A table can contain a maximum of 1000 columns.

12. The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)

13. The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You must update the values of UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in the univ.i source file.

14. InnoDB tables do not support FULLTEXT indexes.

    MYISAM Features

    1. No Transaction support
    2. Table level locking
    3. Provides Full Text search
    4. No limit to data in table.
    5. fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
    6. full text indexing
    7. smaller disk footprint
    8. very high table compression (read only)
    9. spatial data types and indexes (R-tree)
15. By using DATA DIRECTORY='/path/to/data/directory' or INDEX DIRECTORY='/path/to/index/directory' you can specify where the MyISAM storage engine should put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.

you can find more detail at http://faisalbhagat.blogspot.com/2014/09/innodb-vs-myisam.html

Cephalization

Answer 10

Here is a description of differences between InnoDB and MyIsam:

Differences between InnoDB and MyIsam

Few differences:

• MYISAM doesnt support any database transactions,
• INNODB will provide transactions
• MYISAM provides a table level locking,
• INNODB provides a row level locking
• INNOBD supports foreign keys, MYISAM does not...

Jacobsohn

Answer 11

MyISAM is more convienient when it comes to backup, since it's rather simple to just lock all tables and copy the files directly in the filesystem. (mysqlhotcopy which is a perl-script is even part of mysql afaik)

InnoDB is a little more complex and just copying the files won't do since they cannot be restored on another machine out-of-the-box.

However, there are commercial software that offers InnoDB hotcopying.

Tyro

Actually, no. InnoDB is more convenient for backups because it can provide a consistent snapshot of a database without locking every table. mysqldump --single-transaction does this. MyISAM requires you to shut down the database when you're copying the files. – Debouchment

When I said "more convenient" I ment that it is faster, both when dumping and restoring, since you can just copy the datafiles. You don't need to shut down the database, only lock the tables. If you want a consistent copy, then you only need to lock all tables before copying, however it is less intrusive to just lock the table that you are copying. But yes, a innoDB dump would allow the database to be written to during the operation, but a large table can take forever to dump, (several hours) in opposite to a MyISAM file copy which would finish in a matter of minutes. – Tyro

Answer 12

While transaction support is the major difference, table-level locking can be an issue if you have long-running SELECT queries mixed with UPDATE statements.

Aymara