MySQL Profiling

Category: MySQL

Find out why a query is taking a long time to execute.

Log in to MySQL:

SET profiling = 1;
*Execute Your Query*
SHOW PROFILES;
(look for Query_ID for the Query that was executed)
SHOW PROFILE FOR QUERY 1;

Output for Show Profiles;

------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------+
|        2 | 0.02469525 | show databases                                                                                                        |         
------------------------------------------------------------------------------------------------------------------------------------------------+

Output for SHOW PROFILE FOR QUERY;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000176 |
| checking permissions | 0.000010 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000032 |
| init                 | 0.000060 |
| System lock          | 0.000022 |
| optimizing           | 0.000038 |
| statistics           | 0.000643 |
| preparing            | 0.000047 |
| Creating tmp table   | 0.000049 |
| Sorting result       | 0.000014 |
| executing            | 0.000006 |
| Sending data         | 4.591602 |
| Creating sort index  | 0.000871 |
| end                  | 0.000007 |
| removing tmp table   | 0.000008 |
| end                  | 0.000004 |
| query end            | 0.000004 |
| closing tables       | 0.000009 |
| freeing items        | 0.000275 |
| cleaning up          | 0.000052 |
+----------------------+----------+

This will let you know why a query is taking a long time to execute.

Tags: