MySQL Profiling#

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

Login 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 at what process the query is taking the most time.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-2) was last changed on 24-May-2017 15:30 by Hyve Support
G’day (anonymous guest) My Prefs
  • View Page Source
  • This clear IPSec security association,
    clear ipsec sa peer X.X.X.X
    

All Pages

Page views: 1790

Private Tomcat

Linux

MySQL

Email

SQL Server

ASP

JSP

C#

Web Mail

Windows Plesk

Linux Plesk

PHP

Gaming

ASP.NET

Persits ASPUpload

Wiki Help

Referring Pages:
...nobody

JSPWiki v2.8.1