How to record the use of MySQL performance query process _php Tutorial

All from an experiment, consider the following example:

Table:
Copy the Code code as follows:
CREATE TABLE IF not EXISTS ' foo ' (
' A ' int (ten) unsigned not NULL auto_increment,
' B ' int (ten) unsigned not NULL,
' C ' varchar (+) not NULL,
PRIMARY KEY (' a '),
KEY ' Bar ' (' B ', ' a ')
) Engine=innodb;

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

) Engine=myisam;

I inserted 30w data into two tables (performance difference when inserting InnoDB slower than MyISAM)
Copy the Code code as follows:

$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:

One query will be so much different!! InnoDB and MyISAM, quickly analyze and analyze why.

The first is to use explain to view

Determine that both sides are not using index, the second query rows, and MyISAM query rows than InnoDB less so much, but the query is slower than innodb!! This is a little bit strange about Y.

There's a cow. Tool profile

For specific use, refer to: http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

To use the method simply:

Copy the 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 the select from the disk and returning the result to the client. This process will have a lot of IO operations. You can use show Profile CPU for query XX, to see that MyISAM Cpu_system is much larger than Innnodb. At this point it is possible to conclude that MyISAM is slower than InnoDB to make a table query (a query that can be done only by using an index).

http://www.bkjia.com/PHPjc/326869.html www.bkjia.com true http://www.bkjia.com/PHPjc/326869.html techarticle all from an experiment, see the following example: Table: Copy the Code code as follows: CREATE table IF not EXISTS ' foo ' (' a ' int (unsigned) = not NULL auto_increment, ' B ' I NT (Ten) U ...

