MySQL Sub-table combat

This article focuses on how to use stored procedures to complete this table. No other issues are discussed. First we have to look at the manual on the Meger Engine:

The MERGE storage engine, also known as the Mrg_myisam engine, is the same set of MyISAM tables that can be used as a single . "Same" means that all tables have the same column and index information. You cannot merge a table in which columns are listed in different order, no tables that are exactly the same column, or tables that have different sequential indexes. Furthermore, any or all of the tables can be compressed with myisampack .

First look at the table T3 to be divided, a total of 5 million records. The T3 table is structured as follows:

CREATE TABLE' T3 ' (
' ID ' Int(11)Not NULL auto_increment,
' Name ' Varchar (50) default Null Post ' Text PRIMARY KEY ( ' ID ' ),
UNIQUE KEY ' ID ' ( ' ID ' )
) =myisam Auto_increment= 1 DEFAULT charset=utf8 ;

No test data? Then run this stored procedure to write some of it.

-- Procedure "Insert_isam"Ddl

CREATE PROCEDURE' Insert_isam '(InchItem integer)
Begin
DECLARE counterInt;
SetCounter=Item;
WhileCounter>= 1 do
insert into T3 Values (counter,concat ( ' mysqlsystems.com ' ,counter repeat (,10 set counter = counter - 1 end while end

To execute a query:

Call Insert_isam(5000000);

This will have the test data, note that this process is a bit slow, have tried to insert 100 million records, the result of the crash. Since the resulting data are continuous data, so according to the ID of the interval to sort, we have this table according to each table 250,000 to split, of course, the structure of these tables should be consistent with T3, And the storage engine is going to be myisam. As to how many tables we can't determine, but we can use the principle of pagination to split, just learn the stored procedure, try to write a

-- Procedure "Split_table"Ddl

CREATE PROCEDURE' Split_table '(InchPageSizeInt)
Begin
Set @totalRecord=(SelectCount(Id) FromT3);
Set @totalPage=Ceiling(@totalRecord/PageSize);
Set @page=1;
While @page<=@totalPage Do
Set @tableName=Concat("Tab_",@page);
Set @droptable=Concat("DROP table if exists",@tableName);
Prepare SFrom @droptable;
Execute S;
Set @sql=Concat("
CREATE TABLE ",@tableName," (
' id ' int (one) auto_increment,
' Name ' varchar (50),
' Post ' text,
PRIMARY KEY (' id '),
KEY ' id ' (' ID ')
) Engine=myisam; ");
PREPARE STAFrom @sql;
Execute STA;
Set @from=(@page-1)*PageSize+1;
Set @end=@page*PageSize;
Set @sql=Concat("INSERT INTO", @tableName , "SELECT * from T3 where ID between" , @from , "and" , @end prepare sta from @sql execute sta set @page = @page +1 end while end

To execute a query:

Call Split_table(250000);

This process is a bit slow, you need to wait patiently, after the completion of the script, I see that we have generated 20 tab-beginning tables, each table has 250,000 records, so far, we have completed the table splitting process, then we have to create a merge type of table

CREATE TABLE' Taball ' (
' ID ' Int(11)Not NULL auto_increment,
' Name 'varchar(50) DefaultNull,
' Post 'Text,
PRIMARY KEY(' ID '),
KEY' ID ' (' ID ')
)ENGINE=Mrg_myisamUnion=(Tab_1,Tab_2,Tab_3,tab_4,tab_5tab_6,tab_7,tab_8< Span class= "pun",
tab_9,tab_10,< Span class= "PLN" >tab_11,tab_12,tab_13< Span class= "pun" >,tab_14,tab_15,tab_16,tab_17,tab_18,tab_19,tab_20

After execution, look at the Taball table, which records as many as the original table. Do a simple test, respectively, for T3, and Taball execute "select * from T3 limit 250000,10000", compared to the latter a lot faster. At this point, the basic completion of the table. Its advantages and disadvantages still need to be explored!

MySQL Sub-table combat