Record the use of MySQL performance query process _php instance

All stems from an experiment, please see the following example:

Table:

Copy Code code as follows:

CREATE TABLE IF not EXISTS ' foo ' (
' A ' int (a) unsigned not NULL auto_increment,
' B ' int (a) unsigned not NULL,
' C ' varchar not NULL,
PRIMARY KEY (' a '),
KEY ' Bar ' (' B ', ' a ')
) Engine=innodb;

CREATE TABLE IF not EXISTS ' Foo2 ' (
' A ' int (a) unsigned not NULL auto_increment,
' B ' int (a) unsigned not NULL,
' C ' varchar not NULL,
PRIMARY KEY (' a '),
KEY ' Bar ' (' B ', ' a ')

) Engine=myisam;

I inserted 30w of data into two tables (performance difference InnoDB slower than myisam when inserting)

Copy Code code as follows:

<?php

$host = ' 192.168.100.166 ';

$dbName = ' Test ';

$user = ' root ';

$password = ';

$db = mysql_connect ($host, $user, $password) or Die (' db connect failed ');

mysql_select_db ($dbName, $db);

Echo ' ===================innodb======================= '. "\ r \ n";

$start = Microtime (true);

mysql_query ("Select Sql_no_cache sql_calc_found_rows * from foo WHERE b = 1 LIMIT 1000, 10");

$end = Microtime (true);

Echo $end-$start. "\ r \ n";

Echo ' ===================myisam======================= '. "\ r \ n";

$start = Microtime (true);

mysql_query ("Select Sql_no_cache sql_calc_found_rows * from foo2 WHERE b = 1 LIMIT 1000, 10");

$end = Microtime (true);

Echo $end-$start. "\ r \ n";

return Result:

Once the query will be so much different!! InnoDB and MyISAM, hastened analysis and analysis of why.

The first is to use explain to view

Make sure both sides are not using index, the second query check rows, and MyISAM query rows than InnoDB less so much, but the query is slower than innodb!! This y is a little strange.

There's a cow-break tool profile.

Specific use can refer to: http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

Use the method simply:

Copy Code code as follows:

Mysql > Set profiling = 1;

Mysql>show profiles;

Mysql>show profile for Query 1;

In this data, you can see that MyISAM's sending data is much more time-consuming than InnoDB's sending data. View MySQL Documentation

Http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the Longest-running state over the lifetime of a given query.

Sending data is the result of reading a select on disk and returning the result to the client. This process will have a large number of IO operations. You can use show Profiles CPU for query XX to view and find that MyISAM Cpu_system is much larger than Innnodb. The conclusion here is that MyISAM is slower than InnoDB to make a table query (a query that can be completed using only the index).