Create two user tables, user and user2. The table structure is the same, but the user table uses the InnoDB Storage engine, while the user2 table uses the MyISAM storage engine.
-- Table "user" DDL Create Table ' User '('Id' Int ( 11 ) Not Null Auto_increment, 'name' Varchar ( 50 )Default Null , 'Email' Varchar ( 100 ) Default Null , 'Age' Tinyint ( 4 ) Default Null , 'Nickname' Varchar ( 50 ) Default Null , Primary Key ('Id '), Unique Key 'Email '('email '), Key 'Name' ('name '), Key 'Age' ('age') Engine = InnoDB Default Charset = Utf8; -- Table "user2" DDL Create Table 'User2' ('id' Int ( 11 ) Not Null Auto_increment, 'name' Varchar ( 50 ) Default Null , 'Email' Varchar ( 100 ) Default Null , 'Age' Tinyint ( 4 ) Default Null , 'Nickname' Varchar ( 50 ) Default Null , Primary Key ('Id '), Unique Key 'Email '('email '), Key 'Name' ('name '), Key 'Age' ('age') Engine = MyISAM auto_increment = 131610 Default Charset = Utf8;
Insert 10 million pieces of test data to table user & user2.
<? PHP $ Example = Array ( '@ QQ.com', '@ sina.com.cn', '@ 163.com', '@ 126.com', '@ gmail.com', '@ Yahoo.com', '@ live.com', '@ msn.com ', '@ cisco.com', '@ Microsoft.com', '@ ibm.com', '@ apple.com' ); $ Con = Mysql_connect ("Localhost", "root", "your_mysql_password" ); Mysql_select_db ("Index_test ", $ Con ); // Add 10 million test data to table user & user2 For ( $ I = 0; $ I <1, 100000; $ I ++ ){ $ Temp = MD5 ( Uniqid ()); $ Name =Substr ( $ Temp , 0, 16 ); $ Email = Substr ( $ Temp , 8, 12 ). $ Example [ Array_rand ( $ Example , 1 )]; $ Age = Rand (18, 99 ); $ Nickname = Substr ( $ Temp , 16, 16 ); Mysql_query ("Insert into user (name, email, age, nickname) values (' $ Name ',' $ Email ', $ Age ,' $ Nickname ')" ); Mysql_query ("Insert into user2 (name, email, age, nickname) values (' $ Name ',' $ Email ',$ Age ,' $ Nickname ')" );} Mysql_close ( $ Con ); Echo 'Success' ; ?>
Analysis of index usage
Explain select * from user where ID> 100 \ G;
Figure 1
Explain select * From user2 where ID> 100 \ G;
Figure 2
The data in the User table is the same as that in the user2 table, and the index structure is the same, except that they have different storage engines. In Figure 1, the primary key index of primary is used for the query, and the estimated result of the query optimizer is about 65954 rows (actually 131513 rows). In Figure 2, the query does not use an index. Instead, the entire table is scanned. The estimated result returned is 131608 rows (actually 131509 rows ).
Explain select * from user where ID> 100 and age> 50 \ G;
Figure 3
Explain select * from user where ID> 100 and age = 50 \ G;
Figure 4
Explain select * From user2 where ID> 100 and age> 50 \ G;
Figure 5
Explain select * From user2 where ID> 100 and age = 50 \ G;
Figure 6
Share a good blog titled understanding mysql-index and Optimization