Is there a way to get slow-query-log-like profiling information on a single query?

Question

Specifically, I'm looking for this information:

# Query_time: 0.011922  Lock_time: 0.000088  Rows_sent: 107  Rows_examined: 663  Rows_affected: 0  Rows_read: 107
# Bytes_sent: 4152  Tmp_tables: 3  Tmp_disk_tables: 3  Tmp_table_sizes: 259964

on queries I run in the console.

We're running Percona 5.5.

Answer 1

I think you're looking for the "show profiles" query. See http://dev.mysql.com/doc/refman/5.5/en/show-profiles.html

Answer 2

This is being done in MySQL v5.6.5. You have to enable Performance_Schema that's it.

It has a table called events_statements_summary_by_digest Which gives detailed information about query which can be helpful for a DBA/Developer to debug it.

mysql> select * from events_statements_summary_by_digest
         order by count_star desc limit 1 \G
*************************** 1. row ***************************
                     DIGEST: ea4e55f4baf87c09a796bf0d18922323
                DIGEST_TEXT: SELECT @ @ version_comment LIMIT ? 
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 461333000
             MIN_TIMER_WAIT: 461333000
             AVG_TIMER_WAIT: 461333000
             MAX_TIMER_WAIT: 461333000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2012-10-02 17:31:07
                  LAST_SEEN: 2012-10-02 17:31:07
1 row in set (0.00 sec)

For more information you can refer this blog post:

http://www.markleith.co.uk/2012/07/04/mysql-performance-schema-statement-digests/