Not-so-well-known differences between MyISAM and Innodb MySQL server storage engines
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.
Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production. Use the icons below to share this tutorial with your friends.