Mysql index using full analysis _mysql

Create 2 User table users, User2, and the same table structure, but the user table uses the InnoDB storage engine, and the User2 table uses the MyISAM storage engine.

Copy Code code as follows:

--Table "user" DDL
CREATE TABLE ' user ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar DEFAULT NULL,
' Email ' varchar (MB) DEFAULT NULL,
' Age ' tinyint (4) DEFAULT NULL,
' Nickname ' varchar 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 (one) not NULL auto_increment,
' Name ' varchar DEFAULT NULL,
' Email ' varchar (MB) DEFAULT NULL,
' Age ' tinyint (4) DEFAULT NULL,
' Nickname ' varchar DEFAULT NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' email ' (' email '),
KEY ' name ' (' name '),
KEY ' age ' (' age ')
) Engine=myisam auto_increment=131610 DEFAULT Charset=utf8;

Insert 10W test data into table user & User2, respectively.

Copy Code code as follows:

<?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 10W test data to table user & User2
For ($i =0 $i <100000; $i + +)
{
$temp = MD5 (uniqid ());
$name = substr ($temp, 0, 16);
$email = substr ($temp, 8). $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 usage of indexes
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 the data in the User2 table, and the index structure is the same, except that they have different storage engines. In Figure 1, the query uses the primary primary key index, and the query optimizer estimates that the result is about 65954 rows (actually 131513); in Figure 2, the query does not use the index, but it scans the entire table and returns the estimated results in 131608 rows (actually 131509).
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