Bug #76284 Prepared statements do not track status like statistics
Submitted: 12 Mar 2015 10:39 Modified: 12 May 2015 20:12
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.7 OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 2015 10:39] Mark Leith
Description:
The performance_schema.prepared_statements_instances table is not exposing statistics on number of rows touched, or full table scans, etc. correctly:

mysql> EXECUTE scans;
+------+------+
| i    | j    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM performance_schema.prepared_statements_instances\G
*************************** 1. row ***************************
      OBJECT_INSTANCE_BEGIN: 140324557302448
               STATEMENT_ID: 48
             STATEMENT_NAME: scans
                   SQL_TEXT: SELECT i, j FROM scans
            OWNER_THREAD_ID: 20
             OWNER_EVENT_ID: 24573
          OWNER_OBJECT_TYPE: NULL
        OWNER_OBJECT_SCHEMA: NULL
          OWNER_OBJECT_NAME: NULL
              TIMER_PREPARE: 120731000
            COUNT_REPREPARE: 0
              COUNT_EXECUTE: 1
          SUM_TIMER_EXECUTE: 155401000
          MIN_TIMER_EXECUTE: 155401000
          AVG_TIMER_EXECUTE: 155401000
          MAX_TIMER_EXECUTE: 155401000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          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
2 rows in set (0.00 sec)

Note this should have at least 5 in SUM_ROWS_SENT, and 1 in SUM_NO_INDEX_USED.

How to repeat:
USE test;

CREATE TABLE scans (i int, j int) ENGINE = InnoDB;

INSERT INTO scans VALUES (1,1), (2,2), (3,3), (4,4), (5,5);

PREPARE scans FROM 'SELECT i, j FROM scans';

EXECUTE scans;

SELECT * FROM performance_schema.prepared_statements_instances\G

EXECUTE scans;

SELECT * FROM performance_schema.prepared_statements_instances\G

DROP TABLE scans;
[12 May 2015 20:12] Paul DuBois
Noted in 5.7.8, 5.8.0 changelogs.

Prepared statement execution statistics were not correctly tracked in
the Performance Schema prepared_statements_instances table.