Over a million developers have joined DZone.

MySQL 8.0: The End of MyISAM

This post discusses the gradual end of MyISAM in MySQL. See how and why it's winding down and how you can adapt to the change.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

The story that started 20 years ago is coming to its end. I’m talking about the old MyISAM storage engine that was the only storage provided by MySQL in 1995, and was available in MySQL for 20+ years. Actually, part of my job as a MySQL consultant for 10+ years was to discover MyISAM tables and advise customers how to convert those to InnoDB.

(Check your MySQL installation, you may still have MyISAM tables).

MySQL 5.7 still used MyISAM storage for the system tables in the MySQL schema.

In MySQL 8.0 (DMR version as of writing), the MyISAM storage engine is still available. But in a very limited scope:

  • After introducing the new data dictionary, the MyISAM tables are gone from the system schema (“mysql” db).
  • Working with MyISAM is harder now (and discouraged): You can’t just copy MyISAM tables into a running MySQL server. They will not be discovered (unlike InnoDB, where you can use “ALTER TABLE … IMPORT TABLESPACE”)
  • However, you can create a table engine=MyISAM, and it will work as before

InnoDB implemented all the older, missing features:

FeatureMyISAMInnoDB
Full Text IndexesyesSince MySQL 5.6
Portable tables (tablespaces)yesSince MySQL 5.6
Spatial Indexes/RTREE (GIS)yesSince MySQL 5.7
Last update for tableyesSince MySQL 5.7(http://dev.mysql.com/worklog/task/?id=6658)
Suitable for temp tablesyesSince MySQL 5.7Also complex selects uses InnoDB ondisk temp tables
Faster count(*)yes*Faster in MySQL 5.7 but does not store counter

So the only MyISAM advantages left are:

  1. Tables will be smaller on disk compared to uncompressed InnoDB tables.
  2. The count(*) is still much faster in MyISAM:
mysql> select count(*) from a_myisam;
+----------+
| count(*) |
+----------+
|  6291456 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from a_innodb;
+----------+
| count(*) |
+----------+
|  6291456 |
+----------+
1 row in set (2.16 sec)


I would not use MyISAM unless there is a specific case, and for well-known reasons (MyISAM are non-transactional, table level locks, with no crash recovery, etc.)

My colleague Laurynas Biveinis also suggested converting MyISAM to an optional storage engine plugin.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
myisam ,database ,mysql ,innodb

Published at DZone with permission of Alexander Rubin, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}