Of course, this is just a catchy title. As far as I know not all system tables can be converted to InnoDB yet (e.g. grant tables), which makes the header technically false. MyISAM is a very simple engine, and that has some inherent advantages (no transactional overhead, easier to “edit” manually, usually less space footprint on disk), but also some very ugly disadvantages: not crash safe, no foreign keys, only full-table locks, consistency problems, bugs in for large tables,… The 5.7.5 “Milestone 15” release, presented today at the Oracle Open World has an impressive list of changes, which I will need some time to digest, like an in-development (syncronous?) multi-master replication or a revamped query optimizer. But the one very change that I want to highlight today is how the last one of the “big 3” reasons to use MyISAM has finally vanished. For me (and my customers) those reasons were:
Transportable tablespaces
In MyISAM, moving a table in binary format from one server to another was very easy- shutdown the servers and copy the .MYI, .MYD and .frm files. You could even do it in a hot way with the due care: you could copy the table files if you executed the infamous “FLUSH TABLES WITH READ LOCK;” beforehand, and use that as a backup.
innodb_file_per_table was introduced as early as MySQL 4.1, but it wasn’t set as default until 5.6.6 (with a brief indecision on early versions of 5.5). The actual feature “Transportable tablespaces” was added also in 5.6.6, and provided a way inside the server to prepare InnoDB tables for copying, by locking them and exporting its portion of the InnoDB data dictionary (FLUSH TABLES ... FOR EXPORT).
Before 5.6, MySQL required a patch for this to work reliably. Now, single tables can be exported and imported without problem in binary format, even between servers.
Fulltext indexes
Fulltext search has never been the strong point of MySQL (and that is why many people combined it with Sphinx or Apache Lucene/Solr). But many users didn’t require a Google Search clone, only a quick way to search on a smallish website, or a description column, and as we know, BTREE indexes wouldn’t help with like '%term%' expressions.
FULLTEXT indexes and searches have been available since MySQL 3.23.23, but only on MyISAM. I do not know about you, but I have found a relatively high number of customers whose reason to continue using MyISAM was only “we need fulltext search”. Starting with MySQL 5.6.4, fulltext support was added to InnoDB, avoiding the need to decide between transactionality and fast string search. While the starts were not precisely great, (specially compared to other more complex, external solutions) and they were released with some important crashing bugs; the latest changes indicate that InnoDB fulltext support is still being worked on in order to increase its performance.
GIS support
This is the one that MySQL engineers added in MySQL 5.7.5. Of course, GIS datatypes were available since MySQL 4.1 for MyISAM, and in 5.0.16 for most other upstream engines, including InnoDB. However, those types are not useful if they cannot be used quickly in common geographical operations like finding if 2 polygons overlap or finding all points that are close to another. Most of those operations require indexing in 2 dimensions, something that doesn’t work very well with standard BTREE indexes. For that, we need R-Trees or Quadtrees, structures that can efficiently index multidimensional values. Up to now, those SPATIAL indexes, as they are called in MySQL syntax, were only available for MyISAM- meaning that you had to decide again between transactions and foreign keys or fast GIS operations. This was one of the reasons why projects like OpenStreetMap migrated to PostGIS, while others used Oracle Spatial Extensions.
To be fair, the list of changes regarding GIS seems quite extensive, and I have been yet unable to have a detailed look at it. But for I can see there is still no support for projections (after all, that would probably require a full overhaul of this feature), and with it, no native distance functions, which makes it not a viable alternative to PostGIS in many scenarios. But I can see how InnoDB support, at least at MyISAM level and beyond that, is a huge step forward. Again, sometimes you do not need a complete set of features for the main MySQL audience, but a set of minimum options to display efficiently something like a map on a website.
MyISAM in a post-myisam world
In summary, these changes, together with the slow but steady migration of system tables to InnoDB format, plus the efforts on reducing transactional overhead for internal temporary tables will allow Oracle to make MyISAM optional in the future.
I will continue to use MyISAM myself in certain cases because sometimes you do not need a fully ACID storage, and it works particularly well for small, read-only datasets -even if you have millions of those (hey, it works well for WordPress.com, so why should you not use it, too?).
Also, it will take years for all people to adopt 5.7, which is not even in GA release yet.
So tell me, are you planning to migrate engine when 5.7 arrives to your production? What are you still using MyISAM for? Which is your favorite 5.7.5 new feature? Which caveats have you found on the new announced features? Send me a message here or on Twitter.
Yes, once InnoDB supports GIS with indexes, MyISAM will have almost no unique features. Except auto-increment on the second column of a PK, to start renumbering per distinct value in the first column. But this works only when you have table-locks anyway.
One scenario I may still advise to use MyISAM is when a great amount of the write traffic is to one table, probably called “log” or “events” or something. This write traffic is entirely INSERT-only, and it has become too high for the InnoDB redo log to process efficiently. It’s not highly critical data, so it wouldn’t be the end of the world if it got corrupted.
So: ALTER that one table to MyISAM, and relieve the pressure on the redo log for all the other ACID writes. Though I suppose one could also use ARCHIVE for that table, so again we leave MyISAM with nothing to do.