Profiling : A better way to troubleshoot

Another advantage of using the 5.1 version is the avaiability of profiling resources.

Profiling is turned OFF (0) by default. Profiling is turned ON (1) at the session level. The PROFILING_HISTORY_SIZE parameter is used to determine how many statements are kept in the history. The default is 15 statements.

mysql> SET PROFILING=1;

SHOW PROFILES

The SHOW PROFILES command will display the query id, the duration and the SQL command executed.

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------
| Query_ID | Duration | Query
+----------+------------+--------------------------------------------------------
| 1 | 0.00073900 | SELECT Co.Name, Ci.Name, Ci.Population FROM CountryList
| 2 | 0.00086100 | SELECT query_id, seq,state, duration, source_function FROM information_schema.profiling
...


mysql> SHOW PROFILE FOR QUERY 3;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000091 |
| freeing items | 0.000040 |
| logging slow query | 0.000007 |
| cleaning up | 0.000007 |
+--------------------+----------+
4 rows in set (0.00 sec)