Following on from my fun post on Where are they now: MySQL Storage Engines, I thought I’d cover the few storage engines that are really just interfaces to a collection of things. In this post, I’m talking about MERGE.
The MERGE engine was basically a multiplexer down to a number of MyISAM tables. They all had to be the same, there was no parallel query execution and it saw fairly limited use. One of the main benefits was that then you could actually put more rows in a MyISAM table than your “files up to 2/4GB” file system allowed. With the advent of partitioning, this really should have instantly gone away and been replaced by it. It wasn’t.
It is another MySQL feature that exists likely due to customer demand at the time. It’s not a complete solution by any means, PARTITIONING is way more complete and universal…. and much harder to get right inside the MySQL server – which is why MERGE exists. It was easier to write a storage engine that wrapped MyISAM than it was to have any form of partitioning in the server.
One advantage of MERGE tables is it means that you could parallelize myisamchk to repair your broken MyISAM tables after a crash. One step better than no crash safety is at least parallel recovery. The disadvantage being that you’re using MERGE and MyISAM tables.
There is also the great security problem of MRG_MYISAM (the other name for MERGE tables): if you create a MyISAM table t1 and have a user able to access it, if they can create a MERGE table that accesses t1 (say m1) and you then revoke their access to t1, they’ll still be able to access t1 through m1.
MERGE still seems to exist in MySQL 5.6 without even a warning that it’ll go away… which I suspect it will…. we long since got rid of it in Drizzle as, well, what you really want is a query rewrite engine that does views, partitioning etc etc.
Can anyone think of a reason why you should still use MERGE tables in 2013? I can’t.
Ph’nglui mglw’nafh MERGE Storage Engine Mysql wgah’nagl fhtagn?
(“In the house of MySQL, dead MERGE Storage Engine waits dreaming”)
New #mysql planet post : The MERGE storage engine: not dead, just restingâ€¦. or forgotten. http://t.co/4T8GpXzy1N
Antony T Curtis liked this on Facebook.
Here’s a reason: autocaching tables
AFAIK another advantage of MERGE tables over partitioned MyISAM is that you can combine compressed and non-compressed MyISAM tables in one MERGE table.
Why wouldn’t this work with partitioning though?
Also, there isn’t any simple way to copy a partition from a table to another.
Valerii Kravchuk liked this on Facebook.
Pingback: Latest data Industry news round up, Log Buffer #316