As of MySQL 5.7.4, the Performance Schema provides instrumentation for prepared statements, for which there are two protocols:
The binary protocol. This is accessed through the MySQL C API and maps onto underlying server commands as shown in the following table.
The text protocol. This is accessed using SQL statements and maps onto underlying server commands as shown in the following table.
Performance Schema prepared statement instrumentation covers both protocols. The following discussion refers to the server commands rather than the C API functions or SQL statements.
Information about prepared statements is available in the
table. This table enables inspection of prepared statements
used in the server and provides aggregated statistics about
them. To control the size of this table, set the
system variable at server startup.
Collection of prepared statement information depends on the
statement instruments shown in the following table. These
instruments are enabled by default. To modify them, update the
The Performance Schema manages the contents of the
table as follows:
SQLCOM_PREPARE command creates a
prepared statement in the server. If the statement is
successfully instrumented, a new row is added to the
table. If the statement cannot be instrumented,
status variable is incremented.
Prepared statement execution
Execution of a
SQLCOM_PREPARE command for an
instrumented prepared statement instance updates the
Prepared statement deallocation
Execution of a
SQLCOM_DEALLOCATE_PREPARE command for
an instrumented prepared statement instance removes the
table row. To avoid resource leaks, removal occurs even if
the prepared statement instruments described previously
table has these columns:
The address in memory of the instrumented prepared statement.
The internal statement ID assigned by the server. The text and binary protocols both use statement IDs.
For the binary protocol, this column is
NULL. For the text protocol, this
column is the external statement name assigned by the
user. For example, for the following SQL statement, the
name of the prepared statement is
PREPARE stmt FROM 'SELECT 1';
The prepared statement text, with
These columns indicate the event that created the prepared statement.
For a prepared statement created by a client session,
these columns are
NULL. For a prepared
statement created by a stored program, these columns point
to the stored program. A typical user error is forgetting
to deallocate prepared statements. These columns can be
used to find stored programs that leak prepared
SELECT OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME, STATEMENT_NAME, SQL_TEXT FROM performance_schema.prepared_statements_instances WHERE OWNER_OBJECT_TYPE IS NOT NULL;
The time spent executing the statement preparation itself.
The number of times the statement was reprepared internally (see Section 8.9.4, “Caching of Prepared Statements and Stored Programs”). Timing statistics for repreparation are not available because it is counted as part of statement execution, not as a separate operation.
Aggregated statistics for executions of the prepared statement.
columns are the same as for the statement summary tables
(see Section 18.104.22.168, “Statement Summary Tables”).
TRUNCATE TABLE resets the
statistics columns of the table.