MySQL provides us with the ability to create server-side prepared statements, which are precompiled SQL queries that can be executed multiple times with different arguments.
We can use the performance schema to return a list of all prepared statements in the server, along with useful information about each prepared statement.
The prepared_statements_instances Table
The performance_schema.prepared_statements_instances table provides information about prepared statements in the server and provides aggregated statistics about them.
Here’s an example of querying this table:
SELECT * FROM performance_schema.prepared_statements_instances;Example output:
+-----------------------+--------------+----------------------+----------------------------------------+-----------------+----------------+-------------------+---------------------+-------------------+------------------+---------------+-----------------+---------------+-------------------+-------------------+-------------------+-------------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+--------------+-----------------------+------------------+-----------------+
| OBJECT_INSTANCE_BEGIN | STATEMENT_ID | STATEMENT_NAME | SQL_TEXT | OWNER_THREAD_ID | OWNER_EVENT_ID | OWNER_OBJECT_TYPE | OWNER_OBJECT_SCHEMA | OWNER_OBJECT_NAME | EXECUTION_ENGINE | TIMER_PREPARE | COUNT_REPREPARE | COUNT_EXECUTE | SUM_TIMER_EXECUTE | MIN_TIMER_EXECUTE | AVG_TIMER_EXECUTE | MAX_TIMER_EXECUTE | SUM_LOCK_TIME | SUM_ERRORS | SUM_WARNINGS | SUM_ROWS_AFFECTED | SUM_ROWS_SENT | SUM_ROWS_EXAMINED | SUM_CREATED_TMP_DISK_TABLES | SUM_CREATED_TMP_TABLES | SUM_SELECT_FULL_JOIN | SUM_SELECT_FULL_RANGE_JOIN | SUM_SELECT_RANGE | SUM_SELECT_RANGE_CHECK | SUM_SELECT_SCAN | SUM_SORT_MERGE_PASSES | SUM_SORT_RANGE | SUM_SORT_ROWS | SUM_SORT_SCAN | SUM_NO_INDEX_USED | SUM_NO_GOOD_INDEX_USED | SUM_CPU_TIME | MAX_CONTROLLED_MEMORY | MAX_TOTAL_MEMORY | COUNT_SECONDARY |
+-----------------------+--------------+----------------------+----------------------------------------+-----------------+----------------+-------------------+---------------------+-------------------+------------------+---------------+-----------------+---------------+-------------------+-------------------+-------------------+-------------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+--------------+-----------------------+------------------+-----------------+
| 5789404832 | 2 | get_user_by_id | SELECT * FROM users WHERE id = ? | 58 | 91 | NULL | NULL | NULL | PRIMARY | 364000000 | 0 | 3 | 602000000 | 199000000 | 200666000 | 203000000 | 13000000 | 0 | 0 | 0 | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 62016 | 145913 | 0 |
| 5778955696 | 3 | get_user_by_username | SELECT * FROM users WHERE username = ? | 48 | 94 | NULL | NULL | NULL | PRIMARY | 329000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5789406912 | 3 | get_user_by_email | SELECT * FROM users WHERE email = ? | 58 | 95 | NULL | NULL | NULL | PRIMARY | 207000000 | 0 | 2 | 727000000 | 185000000 | 363500000 | 542000000 | 7000000 | 0 | 0 | 0 | 1 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 53776 | 138028 | 0 |
+-----------------------+--------------+----------------------+----------------------------------------+-----------------+----------------+-------------------+---------------------+-------------------+------------------+---------------+-----------------+---------------+-------------------+-------------------+-------------------+-------------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+--------------+-----------------------+------------------+-----------------+
3 rows in set (0.00 sec)
That returned a list of three simple prepared statements that I created for this article. We can see that the table contains information such as the statement name, its definition, owner, how many times the statement has been executed (the count_execute column), etc.
The size of this table can be controlled via the performance_schema_max_prepared_statements_instances system variable at server startup.
Also, collection of prepared statement information depends on certain statement instruments. See the MySQL documentation for details on how it all works.