Using MySQL Merge Storage Engine

MySQL Merge Storage Engine:
Using the Merge storage engine we can create a collection of identical MyISAM tables.  The tables in the collection should have identical column and index information.

Use of Merge Table:
We have many large tables in one of our production databases. For example I am taking the eflearnermaster table. This table has around 75,00,000 rows, and every year around 10,00,000 new rows are added to this table.
So this table can be a candidate of the merge technique. To use merge technique we can create a new tables for storing each year’s data and make a collection all such yearly tables.
Example:
eflearnermaster table is already having 75,00,000 rows I am going to create 5 new tables for holding data of the year 2005, 2006, 2007, 2008 and 2009.
mysql> create table learnermaster2005 like eflearnermaster;
As the original eflearnermaster table is of type InnoDB, we have to convert the new table learnermaster2005 to MyISAM (because merge technique supports only MyISAM tables).
mysql> alter table learnermaster2005 ENGINE=MyISAM;
mysql> create table learnermaster2006 like eflearnermaster;
mysql> alter table learnermaster2006 ENGINE=MyISAM;
mysql> create table learnermaster2007 like eflearnermaster;
mysql> alter table learnermaster2007 ENGINE=MyISAM;
mysql> create table learnermaster2008 like eflearnermaster;
mysql> alter table learnermaster2008 ENGINE=MyISAM;
mysql> create table learnermaster2009 like eflearnermaster;
mysql> alter table learnermaster2009 ENGINE=MyISAM;
All the year wise tables are created, now I will load the data into the tables
mysql> insert learnermaster2005 select * from eflearnermaster where year(dtPhotoUpload)=2005;
mysql> insert learnermaster2006 select * from eflearnermaster where year(dtPhotoUpload)=2006;
mysql> insert learnermaster2007 select * from eflearnermaster where year(dtPhotoUpload)=2007;
mysql> insert learnermaster2008 select * from eflearnermaster where year(dtPhotoUpload)=2008;
mysql> insert learnermaster2009 select * from eflearnermaster where year(dtPhotoUpload)=2009;
All the component tables of my merge table is loaded with data, now I am going to create the merge table
mysql> create table learnermaster like learnermaster2009;
After the table is created now I will alter it and make it a merge table
mysql> alter table learnermaster ENGINE=MERGE UNION(learnermaster2005, learnermaster2006, learnermaster2007, learnermaster2008, learnermaster2009) INSERT_METHOD=LAST;
The new merge table learnermaster will be a union of the tables learnermaster2005, learnermaster2006, learnermaster2007, learnermaster2008 and learnermaster2009. We can perform select, insert, update, delete on the merge table (which will perform the operation on the underlying tables).
The INSERT_METHOD=LAST specifies that the new inserts to learnermaster table will be in the last table of the collection, in our case in learnermaster2009.
So we can use this merge table learnermaster as like our old eflearnermaster table. Also we can use the year wise tables.
Benefits:

• Suppose we know that we need data only for the year 2009, then we can directly access the table learnermaster2009 (which is much smaller than our original eflearnermaster table) and the access will be faster (In our case mostly we need current year data, so it will be beneficial for us).
• If we are not sure then we can access the learnermaster table, which in turn will access all the underlying tables. It is something like we are accessing the eflearnermaster table.
• We can add/remove tables to or from the collection by a simple alter command. Suppose for storing the data of the year 2010, we can add a new table learnermaster2009 and alter the learnermaster to add this learnermaster2010 table into the collection.

Disadvantages:

• Merge storage engine supports only MyISAM tables. So we will lose the transaction safe capability, which is a major concern.
• Data change in the table will take more time as the whole table will be locked during the change.
• Changes in our current code will be required to take the benefit of merge storage engine.
• The MERGE table cannot maintain uniqueness constraints over the whole table as the data goes into one of the underlying table and MySQL ensures that the data is unique within that underlying table.

জয় আই অসম,

প্রণব শর্মা