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.