MySQL Profiler: a simple and convenient query profiling tool

Today I was unexpectedly surprised at how convenient MySQL is. ;-)

I want to bring to your attention the MySQL feature - profiling.
It appeared since version 5.0.37.

Just a couple of requests you can find out what requests form the page (for web developers)
and why it slows down.
')
Earlier, similar functionality was available, but using the query log is not an example more difficult.

So, how to use:

 mysql> set profiling = 1;
 mysql> select count (*) from comment;
 mysql> select count (*) from message;
 mysql> show profiles;

 + ---------- + ------------ + ------------------------- ----- +
 |  Query_ID |  Duration |  Query |
 + ---------- + ------------ + ------------------------- ----- +
 |  1 |  0.00012700 |  select count (*) from comment |
 |  2 |  0.00014200 |  select count (*) from message |
 + ---------- + ------------ + ------------------------- ----- +
 2 rows in set (0.00 sec)

Voila! All requests made per session with runtime.

In my opinion it is very simple and super-convenient.

You can go further and find out in detail what time was spent on each request:

 mysql> show profile for query 1;
 + -------------------------------- + ---------- +
 |  Status |  Duration |
 + -------------------------------- + ---------- +
 |  starting |  0.000015 |
 |  checking query cache for query |  0.000021 |
 |  checking permissions |  0.000003 |
 |  Opening tables |  0.000007 |
 |  System lock |  0.000004 |
 |  Table lock |  0.000023 |
 |  init |  0.000005 |
 |  optimizing |  0.000005 |
 |  executing |  0.000025 |
 |  end |  0.000003 |
 |  end |  0.000001 |
 |  query end |  0.000002 |
 |  storing result in query cache |  0.000003 |
 |  freeing items |  0.000003 |
 |  closing tables |  0.000004 |
 |  logging slow query |  0.000002 |
 |  cleaning up |  0.000001 |
 + -------------------------------- + ---------- +
 17 rows in set (0.00 sec)

Learn more about profiling in the article: Using the New MySQL Query Profiler

Upd: as accurately noticed zayceslav show profiles by default shows profiles for 15 requests. The number of requests can be increased using the profiling_history_size parameter, but not more than 100.

 mysql> set profiling = 1;
 mysql> set profiling_history_size = 100;

PS: this is my first post, please do not judge strictly