Databases Reference
In-Depth Information
SHOW STATUS
,
SHOW PROFILE
, and examining individual entries in the slow query log (if
you have Percona Server—standard MySQL doesn't have any additional information
in the log). We'll demonstrate all three techniques for a single query and show you
what you can learn about the query execution from each.
Using SHOW PROFILE
The
SHOW PROFILE
command is a community contribution from Jeremy Cole that's in-
cluded in MySQL 5.1 and newer, and some versions of MySQL 5.0. It is the only real
query profiling tool available in a GA release of MySQL at the time of writing. It is
disabled by default, but can be enabled for the duration of a session (connection) simply
by setting a server variable:
mysql>
SET profiling = 1;
After this, whenever you issue a statement to the server, it will measure the elapsed
time and a few other types of data whenever the query changes from one execution
state to another. The feature actually has quite a bit of functionality, and was designed
to have more, but it will probably be replaced or superseded by the Performance Schema
in a future release. Regardless, the most useful functionality of this feature is to generate
a profile of the work the server did during statement execution.
Every time you issue a query to the server, it records the profiling information in a
temporary table and assigns the statement an integer identifier, starting with 1. Here's
an example of profiling a view included with the Sakila sample database:
10
mysql>
SELECT * FROM sakila.nicer_but_slower_film_list;
[query results omitted]
997 rows in set (0.17 sec)
The query returned 997 rows in about a sixth of a second. Let's see what
SHOW PRO
FILES
(note the plural) knows about this query:
mysql>
SHOW PROFILES;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
| 1 | 0.16767900 | SELECT * FROM sakila.nicer_but_slower_film_list |
+----------+------------+-------------------------------------------------+
The first thing you'll notice is that it shows the query's response time with higher
precision, which is nice. Two decimal places of precision, as shown in the MySQL
client, often isn't enough when you're working on fast queries. Now let's look at the
profile for this query:
10. The view is too lengthy to show here, but the Sakila database is available for download from MySQL's
website.
