Home » What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?

Solutons:


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.

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.

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.

Related Solutions

Why not drop the “auto” keyword? [duplicate]

Your proposal would be rejected on the basis of backward compatibility alone. But let's say for the sake of argument that the standards committee like your idea. You don't take into account the numerous ways you can initialize a variable widget w; // (a) widget...

Recursive to iterative using a systematic method [closed]

So, to restate the question. We have a function f, in our case fac. def fac(n): if n==0: return 1 else: return n*fac(n-1) It is implemented recursively. We want to implement a function facOpt that does the same thing but iteratively. fac is written almost in...

How can I match values in one file to ranges from another?

if the data file sizes are not huge, there is a simpler way $ join input1 input2 | awk '$5<$4 && $3<$5 {print $2, $5-$3+1}' B100002 32 B100043 15 B123465 3 This Perl code seems to solve your problem It is a common idiom: to load the entire...

Javascript difference between “=” and “===” [duplicate]

You need to use == or === for equality checking. = is the assignment operator. You can read about assignment operators here on MDN. As a quick reference as you are learning JS: = assignment operator == equal to === equal value and equal type != not equal !==...

Compiler complains about misplaced else [closed]

Your compiler complains about an misplaced else because, well, there is an else without a preceding if: // ... for (j=1; j<n-i; j++) { if(a[j]<=a[j+1]) { // ... } // END OF IF } // END OF FOR else { continue; } // ... The else in your code does not follow...

Bootstrap – custom alerts with progress bar

/* !important are just used to overide the bootstrap css in the snippet */ .alertContainer { border-radius: 0 !important; border-width: 0 !important; padding: 0 !important; height: auto !important; position: absolute !important; bottom: 15px !important; left:...

How to Garbage Collect an external Javascript load?

Yes, s.onload = null is useful and will garbage collect! As of 2019, it is not possible to explicitly or programmatically trigger garbage collection in JavaScript. That means it collects when it wants. Although there is cases where setting to null may do a GC...

Math programming with python

At first, what you are looking for is the modulo operator and the function math.floor() Modulo from wikipedia: In computing, the modulo operation finds the remainder after division of one number by another (sometimes called modulus). for example: 12%12=0...

Android slide over letters to create a word [closed]

Here some advice you can use: First for each cell you can create an object that represents the state of that cell: class Cell { char mChar; int row,column; boolean isSelected; } then you can create a 2D array of your cells Cell[][] mTable = ... For views you...

Sum two integers in Java

You reused the x and y variable names (hence the variable x is already defined in method main error), and forgot to assign the ints read from the Scanner to the x and y variables. Besides, there's no need to create two Scanner objects. public static void...

Extend three classes that implements an interface in Java

Using this simplified implementation of the library, using method() instead of M(): interface IFC { void method(); } class A implements IFC { public void method() { System.out.println("method in A"); }; } As akuzminykh mentions in their comment You'd write a...

How to set the stream content in PHPExcel? [closed]

Okey, First thing first PHPExcel_Worksheet_MemoryDrawing() can't solve your problem if you insist to use stream content and pass that to your worksheet your PDF will not render your image. But you can use `PHPExcel_Worksheet_Drawing()' if you want to render...

How to remove all files from a directory?

Linux does not use extensions. It is up to the creator of the file to decide whether the name should have an extension. Linux looks at the first few bytes to figure out what kind of file it is dealing with. To remove all non-hidden files* in a directory use: rm...

Hacker used picture upload to get PHP code into my site

Client side validation The validation code you have provided is in JavaScript. That suggests it is code that you use to do the validation on the client. Rule number one of securing webapps is to never trust the client. The client is under the full control of...

First Time HTML5/CSS Site

Semantically, I would suggest using HTML5 elements more. For example, instead of... <div id="header"> <div id="logo"></div> </div> Use instead: (the ID can stay if you want it to) <header> <div id="logo"></div>...