In order to facilitate the performance of testing, table and other work, it is necessary to establish a larger data table. I'm going to build a tens of millions of user tables here first. Step: 1 Create a data table (MyISAM mode storage insert speed is much faster than InnoDB way) data table description Data Volume: 10 million field type: ID: Number uname: Username Ucreatetime: Creation time Age: Create TABLE USERTB (ID Serial, uname varchar (), ucreatetime datetime, age int (one)) Engine=myisamdefault CHARACTER Set=utf8 Collate=utf8_gene Ral_ci auto_increment=1 row_format=compact;2 Create insert data stored procedure delimiter $ $SET autocommit = 0$$
CREATE PROCEDURE test1 () Begindeclare v_cnt decimal () default 0;dd: Loop insert INTO USERTB values (null, ' User 1 ', ' 2010-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 Execute stored procedure call test1; time: It takes only 95 seconds to execute with i5 notebook 4 Modify engineer as needed (non-essential steps, if no conversion required) ALTER TABLE USERTB engine= InnoDB, time consuming: It takes only 200 seconds to execute with a i5 notebook
Making TENS test tables in MySQL database
