[MySQL] View the execution time consumption of the Create statement via profiles

One, view the status value of the profiles
1. To see if the profiles is already open, the default is not to open it.
Mysql> Show Profiles;
Empty Set (0.02 sec)

Mysql> show variables like '%pro% ';
+---------------------------+-------+
| variable_name             | Value |
+---------------------------+-------+
| have_profiling             | yes   |
| profiling                 | off   |
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| proxy_user                |        |
| slave_compressed_protocol | off   |
+---------------------------+-------+
6 rows in Set (0.00 sec)

I look at the profiles inside there is nothing, so the company server inside profile is not open, I looked at the MySQL variable, sure enough is off.

Two, open profiles, then test
1, open profiles
Mysql> set profiling=1;
Query OK, 0 rows Affected (0.00 sec)

  2,check the status value, which is already turned on
mysql> Show Variables like '%pro% ';
+---------------------------+-------+
| variable_name             | Value |
+---------------------------+-------+
| have_profiling             | yes   |
| profiling                 | on    |
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| proxy_user                |        |
| slave_compressed_protocol | off   |
+---------------------------+-------+
6 rows in Set (0.00 sec)

3, test
Mysql> drop table if exists TEST_CPNY.LISTED_CPNY_FIN_RPT_PRD;
Query OK, 0 rows affected (0.08 sec)

Mysql> CREATE TABLE TEST_CPNY.LISTED_CPNY_FIN_RPT_PRD
SELECT dat.*
-From Test.qa_cpny ES
-INNER JOIN CDP.LISTED_CPNY_FIN_RPT_PRD dat
-On (es.excel_id = dat.excel_id)
;
Query OK, 60960 rows Affected (30.00 sec)
records:60960 duplicates:0 warnings:0

Mysql>

4. View the result values:
Mysql> Show Profiles;
+----------+------------+-------------------------------------------------------------------------------------- ---------------
| query_id | Duration | Query
+----------+------------+-------------------------------------------------------------------------------------- ---------------
| 1 | 0.00044300 | Show variables like '%pro% '
| 2 | 0.05818800 | drop table if exists TEST_CPNY.LISTED_CPNY_FIN_RPT_PRD
| 3 | 30.0030300 | CREATE TABLE TEST_CPNY.LISTED_CPNY_FIN_RPT_PRD
SELECT dat.*
From Test.qa_cpny ES
INNER JOIN CDP.LISTED_CPNY_FIN_RPT_PRD dat
On (es.excel_id = dat.excel_id) |
+----------+------------+-------------------------------------------------------------------------------------- ---------------
3 Rows in Set (0.00 sec)

Mysql>

5, check again
Mysql> Show profile for Query 3;
+------------------------------+----------+
| Status | Duration |
+------------------------------+----------+
| Starting | 0.000072 |
| Checking Permissions | 0.000007 |
| Checking Permissions | 0.000003 |
| Checking Permissions | 0.000007 |
| Opening Tables | 0.000161 |
| System Lock | 0.000013 |
| init | 0.000006 |
| Checking Permissions | 0.000065 |
| Creating Table | 0.003520 |
| After Create | 0.000089 |
| System Lock | 0.000011 |
| Optimizing | 0.000011 |
| Statistics | 0.000016 |
| Preparing | 0.000010 |
| Executing | 0.000003 |
| Sending Data | 29.96599 |
| Waiting for query Cache lock | 0.000014 |
| Sending Data | 0.010215 |
| End | 0.000016 |
| Query End | 0.000004 |
| Closing Tables | 0.000016 |
| Freeing items | 0.000042 |
| Logging Slow Query | 0.000004 |
| Cleaning Up | 0.000004 |
+------------------------------+----------+
Rows in Set (0.00 sec)

It seems that time is consumed in | Sending Data | 29.96599 | up there!

Added on 2014-09-16

• profiling_history_size

  The number of statements for which to maintain profiling information if is profiling enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively disables profiling. See section 13.7.5.32, " SHOW PROFILES Syntax".

[MySQL] View the execution time consumption of the Create statement via profiles