2 ways to view the elapsed time of a Mysql statement _mysql

When the website runs very slowly, I especially know why so slow, so I check, the database is absolutely an important part of the running SQL is absolutely can not let go. Usually when doing projects, I will also pay attention to the writing of SQL statements, write some efficient SQL, so I will often test the SQL statements I wrote. I made a summary of the two methods I know.

One, show profiles and the like to see

1, check the profile is not open, the default is not open.

Mysql> show Profiles; 
Empty Set (0.02 sec) 
mysql> show variables like "%pro%"; 
+---------------------------+-------+ 
| variable_name | Value | 
+---------------------------+-------+ 
| profiling | Off | 
| Profiling_history_size | | 
| protocol_version | Ten | 
| Slave_compressed_protocol | Off | 
+---------------------------+-------+ 
4 rows in Set (0.00 sec)

I check profiles inside nothing, so the company's computer inside profile is not open, I looked at the MySQL variable, it is off.

2, open profile, then test

Open profile

Mysql> set profiling=1; 
Query OK, 0 rows Affected (0.00 sec)

The test is as follows:

Mysql> Show tables; +----------------+ 
| 
Tables_in_test | +----------------+ 
| 
AA | | 
bb | | 
Comment | | 
String_test | | 
user | 
+----------------+ 5 rows in Set (0.00 sec) mysql> select * from AA; +----+------+------------+------+ 
| ID | name | Nname | 
sex | +----+------+------------+------+ 
| 2 | Tank | bbbb,4bbbb | 
NULL | | 3 | Zhang| 3,c,u | 
NULL | 
+----+------+------------+------+ 2 rows in Set (0.00 sec) mysql> Update AA set name= ' d '; 
Query OK, 2 rows Affected (0.00 sec) rows matched:2 changed:2 warnings:0 mysql> Delete from BB; 
Query OK, 2 rows Affected (0.00 sec) mysql> Show profiles; +----------+------------+------------------------+ 
| query_id | Duration | 
Query | +----------+------------+------------------------+ 
| 1 | 0.00054775 | 
Show Tables | | 2 | 0.00022400 | 
SELECT * FROM AA | | 3 | 0.00026275 | 
Update AA set name= ' d ' | | 4 | 0.00043000 | 
Delete from BB | +----------+------------+------------------------+ 4 rows in set(0.00 sec) mysql> Show profile; +----------------------+-----------+ 
| Status | 
Duration | +----------------------+-----------+ 
| (initialization) | 
0.0000247 | | Checking Permissions | 
0.0000077 | | Opening Tables | 
0.0000099 | | System Lock | 
0.000004 | | Table Lock | 
0.000005 | | init | 
0.0003057 | | Query End | 
0.0000062 | | Freeing items | 
0.000057 | | Closing Tables | 
0.000008 | | Logging Slow Query | 
0.0000015 | 
+----------------------+-----------+ rows in Set (0.00 sec) mysql> Show profiles for query 1; +----------------------+-----------+ 
| Status | 
Duration | +----------------------+-----------+ 
| (initialization) | 
0.000028 | | Checking Permissions | 
0.000007 | | Opening Tables | 
0.0000939 | | System Lock | 
0.0000017 | | Table Lock | 
0.0000055 | | init | 
0.000009 | | Optimizing | 
0.0000027 | | Statistics | 
0.0000085 | | Preparing | 
0.0000065 | | Executing | 
0.000004 | | Checking Permissions | 
0.000258 | | Sending Data | 
0.000049 | | End | 
0.0000037 | | Query End | 
0.0000027 | | Freeing items | 
0.0000307 | | Closing Tables | 
0.0000032 | | removing TMP table | 
0.0000275 | | Closing Tables | 
0.0000037 | | Logging Slow Query | 
0.000002 | 
+----------------------+-----------+ rows in Set (0.00 sec) mysql> Show profiles for Query 3; +----------------------+-----------+ 
| Status | 
Duration | +----------------------+-----------+ 
| (initialization) | 
0.0000475 | | Checking Permissions | 
0.0000077 | | Opening Tables | 
0.000026 | | System Lock | 
0.0000042 | | Table Lock | 
0.0000045 | | init | 
0.0000205 | | Updating | 
0.0000787 | | End | 
0.0000567 | | Query End | 
0.000004 | | Freeing items | 
0.0000067 | | Closing Tables | 
0.000004 | | Logging Slow Query | 
0.000002 | +----------------------+-----------+ rows in Set (0.00 sec)

Second, Timestampdiff to check the test time

Mysql> set @d=now (); 
Query OK, 0 rows Affected (0.00 sec) 
mysql> select * from comment; 
+------+-----+------+------------+---------------------+ 
| c_id | mid | name | content | datetime | 
+------+-----+------+------------+---------------------+ 
| 1 | 1 |?? | 2222222211 | 2010-05-12 00:00:00 | 
| 2 | 1 |?? | ???? (??) | 2010-05-13 00:00:00 | 
| 3 | 2 | Tank |?????? | 0000-00-00 00:00:00 | 
+------+-----+------+------------+---------------------+ 
3 rows in Set (0.00 sec) 
mysql> Select Timestampdiff (Second,@d,now ()); 
+--------------------------------+ 
| Timestampdiff (Second,@d,now ()) | 
+--------------------------------+ 
| 0 | 
+--------------------------------+ 
1 row in Set (0.00 sec) 

This method has one thing to note is that three of SQL statements to be implemented together as far as possible, otherwise the error is too large, is not allowed

Set @d=now (); 
SELECT * from comment; 
Select Timestampdiff (Second,@d,now ()); 

If you are using the command line, one thing to note is that in the Select Timestampdiff (Second,@d,now ()), after that, you must copy a blank line, or the last SQL you press ENTER to perform, this is not allowed.

In fact, I think, really want us to care about is those query slow SQL, because the real impact of the speed is that they, about the slow query things, have time to write.