Parsing SQL statements by show profile

Set profling=1;

Select COUNT (*) from Xuehao;

Show Profiles;

Show profile for query 1;

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

Mysql> Select COUNT (*) from Xuehao;
+----------+
| COUNT (*) |
+----------+
| 8 |
+----------+
1 row in Set (0.00 sec)

Mysql> Show Profiles;
+----------+------------+-----------------------------+
| query_id | Duration | Query |
+----------+------------+-----------------------------+
| 1 | 0.00034675 | Select COUNT (*) from Xuehao |
+----------+------------+-----------------------------+
1 row in Set, 1 Warning (0.02 sec)

Mysql> Show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| Starting | 0.000142 |
| Checking Permissions | 0.000015 |
| Opening Tables | 0.000049 |
| init | 0.000036 |
| System Lock | 0.000021 |
| Optimizing | 0.000017 |
| Executing | 0.000023 |
| End | 0.000005 |
| Query End | 0.000003 |
| Closing Tables | 0.000012 |
| Freeing items | 0.000011 |
| Cleaning Up | 0.000015 |
+----------------------+----------+
Rows in Set, 1 warning (0.14 sec)

MySQL supports further selection of detail types such as All,cpu,block io,context,switch,page faults to see what resources the MySQL consumes over time. For example, select the time consuming to view the CPU

Mysql> Show profile CPU for query 1;
+----------------------+----------+----------+------------+
| Status | Duration | Cpu_user | Cpu_system |
+----------------------+----------+----------+------------+
| Starting | 0.000142 | 0.000000 | 0.000000 |
| Checking Permissions | 0.000015 | 0.000000 | 0.000000 |
| Opening Tables | 0.000049 | 0.000000 | 0.000000 |
| init | 0.000036 | 0.000000 | 0.000000 |
| System Lock | 0.000021 | 0.000000 | 0.000000 |
| Optimizing | 0.000017 | 0.000000 | 0.000000 |
| Executing | 0.000023 | 0.000000 | 0.000000 |
| End | 0.000005 | 0.000000 | 0.000000 |
| Query End | 0.000003 | 0.000000 | 0.000000 |
| Closing Tables | 0.000012 | 0.000000 | 0.000000 |
| Freeing items | 0.000011 | 0.000000 | 0.000000 |
| Cleaning Up | 0.000015 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+

Mysql> Show profile source for query 1;
+----------------------+----------+-----------------------+------------------+-------------+
| Status | Duration | source_function | Source_file | Source_line |
+----------------------+----------+-----------------------+------------------+-------------+
| Starting | 0.000142 | NULL | NULL | NULL |
| Checking Permissions | 0.000015 | check_access | sql_parse.cc | 5256 |
| Opening Tables | 0.000049 | Open_tables | sql_base.cc | 4911 |
| init | 0.000036 | Mysql_prepare_select | sql_select.cc | 1050 |
| System Lock | 0.000021 | Mysql_lock_tables | lock.cc | 304 |
| Optimizing | 0.000017 | Optimize | sql_optimizer.cc | 138 |
| Executing | 0.000023 | exec | sql_executor.cc | 110 |
| End | 0.000005 | Mysql_execute_select | sql_select.cc | 1105 |
| Query End | 0.000003 | Mysql_execute_command | sql_parse.cc | 4956 |
| Closing Tables | 0.000012 | Mysql_execute_command | sql_parse.cc | 5004 |
| Freeing items | 0.000011 | Mysql_parse | sql_parse.cc | 6264 |
| Cleaning Up | 0.000015 | Dispatch_command | sql_parse.cc | 1774 |
+----------------------+----------+-----------------------+------------------+-------------+
Rows in Set, 1 Warning (0.00 sec)

Parsing SQL statements by show profile