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;
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;