Apparently MySQL quietly (logical fallacy: I assume it was quiet because I didn’t hear anything about it) slipped a query profiler patch in MySQL 5.0 (as early as 5.0.37) that will give you time statistics about each step of a query’s lifetime. From the aforementioned:

mysql> show profile for query 1;
+--------------------+------------+
| Status             | Duration   |
+--------------------+------------+
| (initialization)   | 0.00006300 |
| Opening tables     | 0.00001400 |
| System lock        | 0.00000600 |
| Table lock         | 0.00001000 |
| init               | 0.00002200 |
| optimizing         | 0.00001100 |
| statistics         | 0.00009300 |
| preparing          | 0.00001700 |
| executing          | 0.00000700 |
| Sending data       | 0.00016800 |
| end                | 0.00000700 |
| query end          | 0.00000500 |
| freeing items      | 0.00001200 |
| closing tables     | 0.00000800 |
| logging slow query | 0.00000400 |
+--------------------+------------+
15 rows in set (0.00 sec)

This looks immensely useful! And while the MySQL documentation is mute on the matter, according to comments on the blog of the original author, the times reported aren’t specific to a thread. In other words, you’ll only get reliable numbers when there’s only one thread running.

Note: this seems to have been left out of the version of MySQL available in Gentoo portage. We’re not sure why, but we have some good minds (i.e., not mine) trying to figure out where it went. Consequently, I haven’t actually used it yet.