Simple MySQL Index Analysis

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. Www.2cto.com
-- Table "user" ddlcreate 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" ddlcreate 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 respectively. Www.2cto.com <? 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 I <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 ';?> 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