Mysql – Merge multiple InnoDB tables in MySQL

innodbmergeMySQL

I have a big InnoDB table (7 million records) and I need to speed up the insert/update operations.
For instance, this is what the table looks like:

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

I thought about partitioning the table by year:

CREATE TABLE product_2016 (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

CREATE TABLE product_2017 (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

I need to read from the union of all the partitioned tables, so I need to merge them in some way, like you can do with the MERGE engine:

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null
) ENGINE=MERGE UNION=(product_2016, product_2017) INSERT_METHOD=LAST;

Of course I cannot do it in this way because the MERGE engine only works with MYISAM tables.

Is the VIEW an alternative? I don't want to slow down the read operation (the read time should be the same). Are there other alternatives?

Best Answer

Mysql has a built-in partitionning system, why not using it instead of creating several tables then merging them ?

CREATE TABLE product ( year int not null, barcode varchar(8) not null, primary key(year, barcode) ) ENGINE=InnoDB PARTITION BY LIST(year)( PARTITION product_2016 VALUES IN (2016), PARTITION product_2017 VALUES IN (2017), PARTITION product_2018 VALUES IN (2018) );

Mysql will split table into 3 files. It will speed up search in index because each index is smaller. It will enhance concurrent access.

But for performance reasons, it is better to partition table in lots of files something like :

CREATE TABLE product ( year int not null, barcode varchar(8) not null, primary key(year, barcode) ) ENGINE=InnoDB PARTITION BY KEY (barcode) PARTITIONS 150 ;

I wonder why you have trouble to insert or update this table, 7 million rows, is not that much, I've got partitionned tables with 270 million rows and I don't have trouble with them.

Do you have concurrent inserts or updates ?

Also as year key is not very discriminant (only 2 values 2016 or 2017), it is better to put bar code first in your primary key like that :

CREATE TABLE product ( year int not null, barcode varchar(8) not null, primary key(barcode, year) ) ENGINE=InnoDB PARTITION BY KEY (barcode) PARTITIONS 150 ;

Not only it will be quicker for Mysql innodb engine to recreate index when you insert a row but there won't be lock only on that file when an insert is done, so other inserts can be done at the same time on other files.

I see that you are talking about MyISAM engine, please note that MYISAM has traditionnaly locks problem, but if your table is partitionned properly, you won't have such lock problem on MYISAM table, so to consider if you have much more selects than inserts/updates and no transactions.

Related Question