Mysql Merge engine sub-table mode

Test the Table News table, the data size is about 150w, the test steps are as follows

1. Create the first table news1, Nsid <750000 data is saved in news1;

CREATE TABLE ' News3 ' (
' NSID ' int (one) is not NULL,
' Newsname ' varchar (+) not NULL,
' Title ' varchar (+) not NULL,
' Content ' Mediumtext not NULL,
' NID ' int (one) is not NULL,
' NIP ' varchar (+) not NULL,
' Editor ' varchar (not NULL),
' Status ' smallint (6) Not NULL,
' Cityid ' int (one) is not NULL,
' CTime ' datetime not NULL,
' Ordernum ' int (one) is not NULL,
' Logoid ' int (one) is not NULL,
' Signtag ' varchar (not NULL),
' Summary ' varchar DEFAULT NULL,
' Type ' smallint (6) Not NULL,
' Pagenum ' int (one) is not NULL,
' Own ' int (one) is not NULL,
' KeyWord ' varchar ($) DEFAULT NULL,
' Wysupport ' int (one) not NULL DEFAULT ' 0 ',
' neweditor ' varchar DEFAULT ',
' Source ' varchar (+) DEFAULT ',
' Remoteid ' varchar DEFAULT NULL,
' BatchId ' int (one) not NULL DEFAULT ' 0 ',
' Utime ' datetime DEFAULT NULL COMMENT ' modified time ',
' Istop ' int (one) not NULL DEFAULT ' 0 ',
' EndTime ' datetime DEFAULT NULL COMMENT ' sticky expiration ',
' Iscomment ' smallint (6) DEFAULT ' 0 ',
' BID ' int (one) not NULL DEFAULT ' 0 ',
' Vtimes ' int (one) is not NULL,
' Tags ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' NSID '),
KEY ' CTime ' (' CTime '),
KEY ' Ordernum ' (' Ordernum '),
KEY ' nid ' (' nid '),
KEY ' Newsname ' (' CTime '),
KEY ' iscomment ' (' BID '),
KEY ' Remoteid ' (' Remoteid '),
KEY ' Cityid ' (' Cityid ', ' CTime ', ' wysupport ', ' Type ', ' Status ')
) Engine=myisam auto_increment=635147 DEFAULT Charset=utf8

2. Create a second table news4,nsid>=750000 data is saved in NEWS4;

CREATE TABLE ' News4 ' (
' NSID ' int (one) is not NULL,
' Newsname ' varchar (+) not NULL,
' Title ' varchar (+) not NULL,
' Content ' Mediumtext not NULL,
' NID ' int (one) is not NULL,
' NIP ' varchar (+) not NULL,
' Editor ' varchar (not NULL),
' Status ' smallint (6) Not NULL,
' Cityid ' int (one) is not NULL,
' CTime ' datetime not NULL,
' Ordernum ' int (one) is not NULL,
' Logoid ' int (one) is not NULL,
' Signtag ' varchar (not NULL),
' Summary ' varchar DEFAULT NULL,
' Type ' smallint (6) Not NULL,
' Pagenum ' int (one) is not NULL,
' Own ' int (one) is not NULL,
' KeyWord ' varchar ($) DEFAULT NULL,
' Wysupport ' int (one) not NULL DEFAULT ' 0 ',
' neweditor ' varchar DEFAULT ',
' Source ' varchar (+) DEFAULT ',
' Remoteid ' varchar DEFAULT NULL,
' BatchId ' int (one) not NULL DEFAULT ' 0 ',
' Utime ' datetime DEFAULT NULL COMMENT ' modified time ',
' Istop ' int (one) not NULL DEFAULT ' 0 ',
' EndTime ' datetime DEFAULT NULL COMMENT ' sticky expiration ',
' Iscomment ' smallint (6) DEFAULT ' 0 ',
' BID ' int (one) not NULL DEFAULT ' 0 ',
' Vtimes ' int (one) is not NULL,
' Tags ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' NSID '),
KEY ' CTime ' (' CTime '),
KEY ' Ordernum ' (' Ordernum '),
KEY ' nid ' (' nid '),
KEY ' Newsname ' (' CTime '),
KEY ' iscomment ' (' BID '),
KEY ' Remoteid ' (' Remoteid '),
KEY ' Cityid ' (' Cityid ', ' CTime ', ' wysupport ', ' Type ', ' Status ')
) Engine=myisam auto_increment=635147 DEFAULT Charset=utf8

3. Create the third table news34, this table is the merge table.

CREATE TABLE ' news34 ' (
' NSID ' INT (one) is not NULL,
' Newsname ' VARCHAR (+) not NULL,
' Title ' VARCHAR (+) not NULL,
' Content ' Mediumtext not NULL,
' NID ' INT (one) is not NULL,
' NIP ' VARCHAR (+) not NULL,
' Editor ' VARCHAR (not NULL),
' Status ' SMALLINT (6) Not NULL,
' Cityid ' INT (one) is not NULL,
' CTime ' DATETIME not NULL,
' Ordernum ' INT (one) is not NULL,
' Logoid ' INT (one) is not NULL,
' Signtag ' VARCHAR (not NULL),
' Summary ' VARCHAR DEFAULT NULL,
' Type ' SMALLINT (6) Not NULL,
' Pagenum ' INT (one) is not NULL,
' Own ' INT (one) is not NULL,
' KeyWord ' VARCHAR ($) DEFAULT NULL,
' Wysupport ' INT (one) not NULL DEFAULT ' 0 ',
' Neweditor ' VARCHAR DEFAULT ',
' Source ' VARCHAR (+) DEFAULT ',
' Remoteid ' VARCHAR DEFAULT NULL,
' BatchId ' INT (one) not NULL DEFAULT ' 0 ',
' Utime ' DATETIME DEFAULT NULL COMMENT ' modified time ',
' Istop ' INT (one) not NULL DEFAULT ' 0 ',
' EndTime ' DATETIME DEFAULT NULL COMMENT ' sticky expiration ',
' Iscomment ' SMALLINT (6) DEFAULT ' 0 ',
' BID ' INT (one) not NULL DEFAULT ' 0 ',
' Vtimes ' INT (one) is not NULL,
' Tags ' VARCHAR (+) DEFAULT NULL,
PRIMARY KEY (' NSID '),
KEY ' CTime ' (' CTime '),
KEY ' Ordernum ' (' Ordernum '),
KEY ' nid ' (' nid '),
KEY ' Newsname ' (' CTime '),
KEY ' iscomment ' (' BID '),
KEY ' Remoteid ' (' Remoteid '),
KEY ' Cityid ' (' Cityid ', ' CTime ', ' wysupport ', ' Type ', ' Status ')
) Type=merge union= (NEWS3,NEWS4) insert_method=last auto_increment=1;

4. Inserting test data

INSERT into NEWS3
SELECT * FROM news WHERE Nsid < 750000

INSERT into News4
SELECT * FROM news WHERE Nsid >= 750000

5.news table and NEWS34 table for test comparison

SELECT * from NEWS34 WHERE Cityid = + and ctime like '%2014% ';
SELECT * from NEWS WHERE Cityid = + ctime like '%2014% ';

SELECT * from NEWS34 WHERE Cityid = + and TYPE = 3 ORDER by NSID;
SELECT * from NEWS WHERE Cityid = + and TYPE = 3 ORDER by NSID;

SELECT * from NEWS34 WHERE Cityid = + and TYPE = 2 and Source = ' Kat House Network collation ' and Vtimes <10 ORDER by Nsid DESC;
SELECT * from NEWS WHERE Cityid = + and TYPE = 2 and Source = ' Kat House Network collation ' and Vtimes <10 ORDER by Nsid DESC;

SELECT * from NEWS34 WHERE Cityid = 2 and TYPE = 2 and Source = ' Kat House Network collation ' and editor= ' sjt ' and ' STATUS = + keyword like '% Gillette% ' and Vtimes <20 ORDER by Nsid DESC;
SELECT * from NEWS WHERE Cityid = + and TYPE = 2 and Source = ' ' and editor= ' sjt ' and STATUS = 2 and keyword like ' % Kat House% ' and Vtimes <20 ORDER by Nsid DESC;

The first set of Sql:myisam tables, the news table, executes slightly faster

Second set of SQL: This set of SQL query conditions is slightly more complex, with where and order, which is faster than the Merge table MyISAM table

Third Group of SQL:

Group Fourth:

Test conclusion:

Overall, the query efficiency gap between MyISAM and merge tables is very small;