Not-so-well-known differences between MyISAM and Innodb MySQL server storage engines

Category:

Tutorials > Database Management Systems > MySQL > Tips and Tricks

There are some very well-known differences between Innodb and MyISAM e.g. Innodb supports transactions, MyISAM does not etc. However, I am goint to list some not-so-well-known differences here for my readers. Please feel free to suggest more through comments below. Everything should be true for MySQL server versions 5.1 or before.

Innodb vs MyISAM:

  • 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.
  • Innodb never fragments short rows.
  • Innodb does not have separate index files so they do not have to be opened.
  • 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.
  • 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.
  • MyISAM requires a file descriptor open for each client for the same table data file. Index file descriptors are shared b/w clients on the other hand. This causes a high usage of open files and causes the database to reach the open files limit allowed for the process (ulimit for Linux external to MySQL or open_files_limit variable inside MySQL).
  • MyISAM can possibly cause information_schema to respond way too late (not confirmed through Innodb yet).
  • For MyISAM tables, table header has to be modified each time the table is opened. (http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/comment-page-1/#comment-773324)
  • In some cases, MyISAM supports building idexes by sorting (after ALTERs).
  • MyISAM databases can be read from readonly media eg. CD.

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

- MyIsam tables can simply be copied from server to server.
- ALTER TABLE ORDER BY secondary index - can make huge speed gains on rotational media Posted by: Steve on Dec 27, 2010

AUTO_INCREMENT is different.

Exercise for the reader to find out where :) Posted by: Stewart S on Dec 28, 2010

Myisam inserts are fast Posted by: Shantanu O on Jan 20, 2011

"Myisam inserts are fast"...

...but Innodb is faster. Posted by: Peter on Aug 15, 2011

Inno Db are used where the tables are used only for selection or where no frequent updates are fired.

Myisam insertion is faster because it inserts one row and finish the transaction but InnoDb holds in all the transaction untill all the rows inserted. If the insertion is hampered in between in case of InnoDb table whole data is reverted back to its state before insertion or any updation begins. Posted by: Jayesh on Aug 30, 2011

There are many differences in INDEXes, etc. See
http://mysql.rjweb.org/doc.php/myisam2innodb

Batching 100 rows in a single INSERT will run 10x faster in either engine. Posted by: Rick J on Feb 27, 2012

MyIsam tables can be repaired with the Repair command and with myisamchk. InnoDb is not supplied with repair tools. Posted by: Wim R on May 22, 2014

leave a comment on tutorial leave a comment