Bug #43804 Table Name In SHOW PROFILE
Submitted: 23 Mar 2009 13:09
Reporter: Mikiya Okuno Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any

[23 Mar 2009 13:09] Mikiya Okuno
Description:
SHOW PROFILE is a strong tool to examine MySQL Server's statistics and behaviors, and SOURCE option to display is really fine. However, the output is sometimes difficult to understand without finding a table in question for each step. For example, see the SHOW PROFILE output below;

mysql> SHOW PROFILE SOURCE;
+--------------------+----------+-----------------------+---------------+-------------+
| Status             | Duration | Source_function       | Source_file   | Source_line |
+--------------------+----------+-----------------------+---------------+-------------+
| starting           | 0.000268 | NULL                  | NULL          |        NULL | 
| Opening tables     | 0.000102 | open_tables           | sql_base.cc   |        4482 | 
| System lock        | 0.000050 | mysql_lock_tables     | lock.cc       |         258 | 
| Table lock         | 0.000056 | mysql_lock_tables     | lock.cc       |         269 | 
| init               | 0.000115 | mysql_select          | sql_select.cc |        2354 | 
| optimizing         | 0.000054 | optimize              | sql_select.cc |         771 | 
| statistics         | 0.000077 | optimize              | sql_select.cc |         953 | 
| preparing          | 0.000056 | optimize              | sql_select.cc |         963 | 
| executing          | 0.000051 | exec                  | sql_select.cc |        1647 | 
| Sending data       | 0.000157 | exec                  | sql_select.cc |        2194 | 
| optimizing         | 0.000046 | optimize              | sql_select.cc |         771 | 
| statistics         | 0.000106 | optimize              | sql_select.cc |         953 | 
| preparing          | 0.000318 | optimize              | sql_select.cc |         963 | 
| end                | 0.000098 | mysql_select          | sql_select.cc |        2399 | 
| query end          | 0.000041 | mysql_execute_command | sql_parse.cc  |        4821 | 
| freeing items      | 0.000112 | mysql_parse           | sql_parse.cc  |        5828 | 
| logging slow query | 0.000044 | log_slow_statement    | sql_parse.cc  |        1628 | 
| cleaning up        | 0.000080 | dispatch_command      | sql_parse.cc  |        1595 | 
+--------------------+----------+-----------------------+---------------+-------------+
18 rows in set (0.01 sec)

Can you determine which function (e.g. JOIN::execute) examines which table? If SHOW PROFILE has an option to store table names, schema name or any other further detailed information, it will be really helpful when tuning queries.

How to repeat:
See SHOW PROFILE output.

Suggested fix:
Storing more information means more CPU time consumption. So, I suggest to have an option to control SHOW PROFILE behavior, like below:

mysql> SET PROFILING_VERBOSITY=HIGH;