Prepared Statements and ProxySQL

Being a MySQL database compliant protocol ProxySQL is able to identify and track prepared statements in MySQL. Apart from tracking statements ProxySQL also maintains an internal Global Prepared Statement Cache in which it enhances the performance of prepared statements by encouraging re-use of “prepared” statements:

  • When a statement is prepared for the first time it is cached
  • When a statement is closed by a frontend it will still be present in the cache
  • If the same statement is prepared it will be served from the cache (avoiding an additional backend call)
  • This mechanism works for the backend, on the frontend applications should PREPARE statements once and EXECUTE multiple times with different variable values (ProxySQL will automatically try to do this on the backend)
    • In case the statement is already prepared on the connection it will be used, in case it is not prepared it will be retried from the Global PS Cache
  • The efficacy of the mechanism can be observed in the stats_mysql_prepared_statements_info table

Query Routing and Prepared Statements

ProxySQL also maintains routing information related to prepared statements, the behavior depends on the version of ProxySQL:

  • ProxySQL 1.3 – 2.0.x: Routing metadata is generated on statement PREPARE, updates to ProxySQL Query Rules require prepared statements to be manually re-prepared in order to be updated with the new routing information and Query Annotations are not evaluated for prepared statements.
  • ProxySQL 2.1.x: Routing metadata is automatically updated by ProxySQL internally while Query Annotations are not evaluated for prepared statements
  • ProxySQL 2.2.x+: Routing metadata and Query Annotations are automatically updated by ProxySQL internally