MySQL partition test in Windows 7

MySQL partition test in Windows 7

1. Check database version information

Select version ();

2. Check whether partitions are supported

Show variables; Show variables like '% partition % ';

3. Create a test data table

// create a users00 table (10 partitions are supported) Create Table users00 ( uid int (10) Not null auto_increment, 'name' varchar (32) default', 'email 'varchar (64) default '', Primary Key (UID) ) engine = MyISAM partition by range (UID) ( partition P1 values less than (10000), partition P2 values less than (20000 ), partition P3 values less than (30000), partition P4 values less than (40000 ), partition P5 values less than (50000), partition P6 values less than (60000 ), partition P7 values less than (70000), partition P8 values less than (80000 ), partition P9 values less than (90000), partition P10 values less than maxvalue ); // create users01 (no partition) Create Table users01 ( uid int (10) not null auto_increment, 'name' varchar (32) default '', 'email 'varchar (64) default '', Primary Key (UID) )

4. Create a stored procedure

// Create the dousers00 stored procedure and insert 10 million data records into the users00 table Delimiter // Create procedure dousers00 () Begin Declare I int default 1; While I <= 100000 do Insert into users00 ('name', 'email ') values ('sunny', 'yuhan8239288 @ 163.com '); Set I = I + 1; End while; End; // // Create the dousers01 stored procedure and insert 10 million data records into the users00 table Delimiter // Create procedure dousers01 () Begin Declare I int default 1; While I <= 100000 do Insert into users01 ('name', 'email ') values ('sunny', 'yuhan8239288 @ 163.com '); Set I = I + 1; End while; End; //

5. Execute the stored procedure to insert data

Call dousers00 (); Call dousers01 ();

6. Test the query speed

Because the execution speed of table users01 is too slow, only 38812 data records are inserted (100000 data records in Table users00)

Run the following command to query the users01 table: Run the following command to query the users00 table: Result In sqlyog Select count (*) from users01 Select count (*) from users00

7. Summary

Due to data insertion, the data in Table users01 is much less than that in Table users00 (when data in table users01 is 2/5 in Table users00 ), however, the execution speed of users01 is 0.023 S. If the conversion time is 0.0575 s based on the proportion of data volume, the execution time of users00 table is compared, the execution speed of table users00 execution dormitory is 1.5 times faster than that of table users01.