Making TENS test tables in MySQL database

Objective:

Recently prepared to learn more about MySQL, including the characteristics of various engines, performance optimization, sub-table sub-library and so on. In order to facilitate the performance of testing, table and other work, you need to first build a larger data table. I'm going to build a tens of millions of user tables here first.

Steps: 1 Creating a data table (MyISAM storage insertion speed is much faster than InnoDB mode )

Data table Description

Data Volume:10 million

field type:

ID: Ref.

uname: User name

Ucreatetime: Creation Time

Age: Ages

2 Creating an Insert data stored procedure

Delimiter $ $SET autocommit = 0$ $create  procedure test1 () Begindeclare v_cnt Decimal (Ten)  default 0;dd: Loop           I Nsert into  USERTB values (null, ' User 1 ', ' 2010-01-01 00:00:00 ', '), (null, ' User 2 ', ' 2010-01-01 00:00:00 ', '), (null, ' User 3 ', ' 2010-01-01 00:00:00 ', ' (null, ' User 4 ', ' 2010-01-01 00:00:00 ', '), (null, ' User 5 ', ' 2011-01-01 00:00:00 ', '), (NULL, ' User 6 ', ' 2011-01-01 00:00:00 ', ' (null, ' User 7 ', ' 2011-01-01 00:00:00 ', '), (null, ' User 8 ', ' 2012-01-01 00:00:00 ', '), (null , ' User 9 ', ' 2012-01-01 00:00:00 ', ' (null, ' user 0 ', ' 2012-01-01 00:00:00 ', ');                  commit;                    Set v_cnt = v_cnt+10;                           If  v_cnt = 10000000 then leave DD;                          End If;         End loop DD; end;$ $delimiter;

3 Executing stored procedures

Call Test1;

Time consuming: It takes only 95 seconds to execute with I5 's Notebook

4 Modify the engineer as needed (non-essential steps if no conversion is required)

ALTER TABLE USERTB ENGINE=INNODB;

Time consuming: It takes only 200 seconds to execute with I5 's Notebook

Making TENS test tables in MySQL database