Databases Reference
In-Depth Information
mysql>
SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000082 |
| Opening tables | 0.000459 |
| System lock | 0.000010 |
| Table lock | 0.000020 |
| checking permissions | 0.000005 |
| checking permissions | 0.000004 |
| checking permissions | 0.000003 |
| checking permissions | 0.000004 |
| checking permissions | 0.000560 |
| optimizing | 0.000054 |
| statistics | 0.000174 |
| preparing | 0.000059 |
| Creating tmp table | 0.000463 |
| executing | 0.000006 |
| Copying to tmp table | 0.090623 |
| Sorting result | 0.011555 |
| Sending data | 0.045931 |
| removing tmp table | 0.004782 |
| Sending data | 0.000011 |
| init | 0.000022 |
| optimizing | 0.000005 |
| statistics | 0.000013 |
| preparing | 0.000008 |
| executing | 0.000004 |
| Sending data | 0.010832 |
| end | 0.000008 |
| query end | 0.000003 |
| freeing items | 0.000017 |
| removing tmp table | 0.000010 |
| freeing items | 0.000042 |
| removing tmp table | 0.001098 |
| closing tables | 0.000013 |
| logging slow query | 0.000003 |
| logging slow query | 0.000789 |
| cleaning up | 0.000007 |
+----------------------+----------+
The profile allows you to follow through every step of the query's execution and see
how long it took. You'll notice that it's a bit hard to scan this output and see where
most of the time was spent. It is sorted in chronological order, but we don't really care
about the order in which the steps happened—we just care how much time they took,
so we know what was costly. Unfortunately, you can't sort the output of the command
with an
ORDER BY
. Let's switch from using the
SHOW PROFILE
command to querying the
corresponding
INFORMATION_SCHEMA
table, and format to look like the profiles we're used
to seeing:
mysql>
SET @query_id = 1;
Query OK, 0 rows affected (0.00 sec)
mysql>
SELECT STATE, SUM(DURATION) AS Total_R,
