CODEWITHSUNDEEP
mysqlsqlprofiler
Sid
Sid

Reputation: 765

Profiler Program for MySQL like in MSSQL

is there a Profiler like in T-SQL namely SQL Server to trace queries for MYSQL.

I am using Windows, XAMPP MySQL. I have this PHP Command that is so simple yet it does not update properly. I want to see if it is being run properly so I would like to trace it like in MSSQL.

Upvotes: 1

Views: 3259

Answers (1)

Bjoern
Bjoern

Reputation: 16304

MySQL has an inbuild profiler, which allows you to see very detailed for what part of the query how much time has been spend.

To enable it, use this statement:

SET profiling = 1;

Then these steps:

(1) Execute your query.

(2) Find out the query id for profiling:

SHOW PROFILES;

It will return you something like this:

Query_ID |  Duration | Query
---------+-----------+-----------------------
   2     | 0.0006200 | SHOW STATUS
   3     | 0.3600000 | (your query here)
  ...    | ...       | ...

Now you know the query id is (3).

(3) Profile the query.

SHOW PROFILE FOR QUERY 3;   // example

This will return you the details, which might look like this:

Status                          | Duration
--------------------------------+-------------------
starting                        | 0.000010
checking query cache for query  | 0.000078
Opening tables                  | 0.000051
System lock                     | 0.000003
Table lock                      | 0.000008
init                            | 0.000036
optimizing                      | 0.000020
statistics                      | 0.000013
preparing                       | 0.000015
Creating tmp table              | 0.000028
executing                       | 0.000602
Copying to tmp table            | 0.000176
Sorting result                  | 0.000043
Sending data                    | 0.080032
end                             | 0.000004
removing tmp table              | 0.000024
end                             | 0.000006
query end                       | 0.000003
freeing items                   | 0.000148
removing tmp table              | 0.000019
closing tables                  | 0.000005
logging slow query              | 0.000003
cleaning up                     | 0.000004

In this example, most of the time was actually spend sending the data from the server back to the client.

Upvotes: 2

Related Questions