How can I check how much time MySQL query, without printing it on the console, is taking?

MySQLMySQLi Database

To check this we need to have the profiling information which indicates resource usage for the statements executed during the course of the current session. Profiling information can get by SHOW PROFILE and SHOW PROFILES statement.

Before running these statements, the profiling session variable must be set to 1 as follows −

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

Now if we will run SHOW PROFILES statement then it will display the list of most recent statements sent to the server along with the duration and query id.

mysql> show profiles;
+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
| 1        | 0.01602200 | drop table if exists t1              |
| 2        | 0.57223450 | create table t1(id int)              |
| 3        | 0.00015425 | show profile for query1              |
| 4        | 0.22945050 | create table e1(id int)              |
| 5        | 0.09977025 | insert into e1(id) values(3),(4),(5) |
+----------+------------+--------------------------------------+
5 rows in set (0.00 sec)

Now suppose we want to check the duration for a particular query then we can run SHOW PROFILE for query query_no statement. For example, if we run the statement for query_id = 5 then following will be the output −

mysql> show profile for query 5;
+------------------------------+--------------+
| Status                       | Duration     |
+------------------------------+--------------+
| starting                     | 0.000138     |
| checking permissions         | 0.000024     |
| Opening tables               | 0.000057     |
| System lock                  | 0.035186     |
| init                         | 0.000035     |
| update                       | 0.021484     |
| Waiting for query cache lock | 0.000021     |
| update                       | 0.000005     |
| end                          | 0.000024     |
| query end                    | 0.042700     |
| closing tables               | 0.000017     |
| freeing items                | 0.000076     |
| logging slow query           | 0.000003     |
| cleaning up                  | 0.000002     |
+------------------------------+--------------+
14 rows in set (0.00 sec)
raja
Published on 21-Feb-2018 11:27:36
Advertisements