29 Jun |
Profiling with MySQL![]() |
Whenever you encounter a query slower than you expected, and you’re sure you’ve done everything in your power to ensure your tables are properly optimized and indexed, you might want to profile it. 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 use it, follow these steps:
(1) Activate the profiler.
Since the profiler is deactivated by default we first have to start it.
SET profiling = 1;
(2) Execute your query.
(3) Find out the query id for profiling.
SHOW PROFILES;
It will return you something like this:
Query_ID | Duration | Query ---------+-----------+----------------------- ... | ... | ... 29 | 0.0006200 | SHOW STATUS 30 | 0.3600000 | SELECT (your query here) ... | ... | ...
Now you know the query id is 30.
(4) Profile the query.
SHOW PROFILE FOR QUERY 30; // 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. Maybe try narrowing down the amount of data you get from the query. Maybe raise your query cache. Maybe do something different – its entirely up to your database, server and needs what to make of this result. But now you know where to look!