- Welcome to Geeksww.com
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.
Did this tutorial help a little? How about buy me a cup of coffee?
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.
tags cloud
popular searches
free download for mysql database server 5.1.5, bison, gearman, source code, php, install cairo, laptop, mysql, java, linux, install mysql, mysql initialization, mysql mysql, tools, ubuntu
Similar Tutorials:
- Download, configure, compile, and install MySQL 5.5 from source code on Linux
- Download all maatkit scripts through a single command
- Some Tricks used by the Linux kernel
- Fast, parallel restore from SQL dumps (mysqldump) for MySQL
- SQL: Union and Union All
Tutorials in 'Database Management Systems > MySQL' (more):
- What should be monitored on a busy Database/MysQL server?
- How to speed up a Mysql replica?
- The server requested authentication method unknown to the client [phpmyadmin]
- How to set default values for MySQL JSON columns
- How to delete rows in table when PK is referenced in another table
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