Working With MyISAM in MySQL

MyISAM is one of the most popular storage engines in MySQL. MyISAM frequently is the second choice after InnoDB – in this blog post we will try to find out how best to work with this engine.

What is MyISAM?

MyISAM is one of MySQL’s storage engines. MyISAM is based on ISAM (Indexed Sequential Access Method), an indexing algorithm developed by IBM that allows retrieving information from large sets of data in a fast way. However, it does not do very well when simultaneously reading from and writing to one table, due to its table locking. It also does not support transactions.

For some MySQL engineers, this engine is the most popular choice after InnoDB: the MyISAM storage engine was the only storage engine provided by MySQL in 2005 and was available to use for more than 20 years. MyISAM was the default storage engine for MySQL up to version 5.5.

MyISAM from the Inside

An illustration of how MyISAM works from the inside is not within the scope of this blog, but we can provide you with the settings that help you optimize performance of the engine:

• Myisam_sort_buffer_size defines the buffer that is allocated when the index is sorted by running REPAIR, CREATE INDEX or ALTER TABLE queries.
• Key_buffer_size defines the size of the buffer used for index blocks across MyISAM tables. Increasing this parameter can lead to better index handling.
• Sort_buffer_size describes the size of a buffer that is allocated for threads that need to do sort operations.
• Read_buffer_size describes the size of a buffer that is allocated for threads that perform sequential scan operations.
• Write_buffer_size describes the size of the write buffer.

These four parameters are important, but while they are important, you should also keep an eye on the key_buffer_size variable. The key_buffer_size variable determines the size of the index buffers held in memory – think of it as the innodb_buffer_pool_size counterpart, but for MyISAM. If your servers consist primarily of MyISAM tables, you could allocate about 25% or more of the available RAM on the server to the key_buffer_size variable. There is also another way to determine what the value of the key_buffer_size parameter should be – simply compare the key_read_requests value (total value of requests to read an index) and the key_reads value (the value of key_reads is the number of requests that had to be read from disk). The values for those parameters can be retrieved by looking at the server status variables (simply issue a SHOW GLOBAL STATUS query on your MySQL server). It is also beneficial to keep in mind that if key_reads returns a large value, the value of key_buffer_size is probably too small.

MyISAM and MySQL 8.0

If you ask some MySQL engineers, they will say that MyISAM should no longer be used. Why? Well, some people say that because of the fact that because when MySQL advanced, they added the majority of the features that previously could only be seen in MyISAM into InnoDB effectively rendering MyISAM obsolete:

• Full-text indexes have been available in InnoDB since version 5.6.
• Portable tablespaces became available in InnoDB since version 5.6.
• Spatial indexes became available in InnoDB since version 5.7.
• Last update for table became available in InnoDB since version 5.7.

So, should you still use MyISAM? Probably not. There is one caveat though – keep in mind that simple COUNT(*) queries will probably perform faster on MyISAM than they will on InnoDB – MyISAM stores the number in the table metadata, InnoDB does not.

I am using MyISAM and want to switch to InnoDB, What Do I Do?

If you still use MyISAM and want to switch to InnoDB, simply convert all of your tables to InnoDB. Obviously that’s easier said than done, but here’s a simple guide:

1. Figure out which tables in your MySQL instance are using MyISAM:

   SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘database_name’ AND ENGINE = ‘MyISAM’;

2. Convert all of your MyISAM tables to InnoDB:

   ALTER TABLE `table_name` ENGINE = InnoDB;

If you do not want to run multiple ALTER TABLE statements one after another, consider putting the ALTER TABLE statements into a loop. That’s it – you’re done!

Summary

MyISAM is one of the most popular MySQL engines. The engine was the default for MySQL versions up to 5.5. The engine is one of the most popular choices after InnoDB, but it can be called obsolete as of MySQL 8.0 – MySQL has already made sure that everything that can be done with MyISAM also can be done when InnoDB is in use, so at this point MyISAM is pretty much only useful if you want simple COUNT(*) queries to be faster. Such queries will be faster because MyISAM stores the number in table metadata – other MySQL engines do not.