Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

Cache MySQL prepared-query results

Experimental prepared-query result caching is available in the published ProxySQL 3.1.12 and 4.0.12 prereleases. Stable 3.0.12 does not include it. It lets eligible binary prepared executions reuse a saved result without executing the query on a backend again.

This caches returned rows. The prepared-statement metadata cache separately saves preparation work. Result caching uses the existing query cache, including its memory budget, expiration, and empty-result policies. No separate enable switch is required: existing rules with positive cache_ttl can also select prepared requests after an upgrade. Review broad cache rules before upgrading.

Choose a read that can reuse an earlier answer

Start with a frequent lookup whose answer may remain unchanged for a short period. For example, an application browsing a catalog may accept a label that was fetched a few seconds earlier.

cache_ttl=5000 permits five seconds of reuse. It does not guarantee that backend data is at most five seconds old: a lagging replica may already return older data. Reading an entry does not restart its lifetime. Writes do not invalidate cached answers, so a read confirming an update should use an uncached path if it must see that update immediately.

ProxySQL does not prove that a SELECT is safe to skip. Exclude queries that depend on temporary tables, untracked session state, permissions that change their meaning, or functions with side effects. Cache only where the application’s freshness and execution requirements permit it.

Add a rule for the prepared lookup

Assume the existing application account catalog_reader connects to schema catalog and has permission to read public_labels(id, label). Its driver prepares this SQL through MySQL’s binary prepared-statement API:

SELECT label FROM public_labels WHERE id = ?

Connect to ProxySQL Admin, choose an unused rule ID, and adapt the account, schema, and SQL spelling:

INSERT INTO mysql_query_rules
  (rule_id, active, username, schemaname, match_pattern,
   cache_ttl, cache_empty_result, apply)
VALUES
  (970101, 1, 'catalog_reader', 'catalog',
   '^SELECT label FROM public_labels WHERE id = [?]$', 5000, 0, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

This rule excludes empty results. cache_empty_result=1 includes them; NULL uses mysql-query_cache_stores_empty_result. Check existing rule order: apply=1 ends evaluation, so this example assumes no later rule must supply another routing action. An earlier terminating rule can also prevent this rule from matching.

Use the application’s normal driver API to prepare once and execute the same lookup repeatedly with the same ID. SQL text commands such as PREPARE ... and EXECUTE ... do not exercise this binary-protocol cache.

Requests that bypass caching

Eligible executions use COM_STMT_EXECUTE with no cursor, iteration count one, and no pending streamed parameter data (COM_STMT_SEND_LONG_DATA), including an empty streamed value. These cases bypass result caching:

  • Active transactions or sessions with autocommit disabled.
  • Sessions locked to a hostgroup.
  • Rules or annotations requiring min_gtid, gtid_from_hostgroup, or max_lag_ms freshness constraints.
  • Locking reads. The conservative check also bypasses SQL containing FOR or LOCK tokens in comments, strings, or identifiers.

Bypassed requests use the normal execution path. Only complete, successful, single result sets are stored. Errors, multiple results, incomplete or already streamed responses, and command-only responses are not cached. Warning handling follows mysql-query_cache_handle_warnings.

Check reuse and memory

Read these counters in Admin before and after repeated application requests:

SELECT Variable_Name, Variable_Value
FROM stats_mysql_global
WHERE Variable_Name IN (
  'Query_Cache_count_GET', 'Query_Cache_count_GET_OK',
  'Query_Cache_count_SET', 'Query_Cache_Memory_bytes',
  'Query_Cache_Entries', 'Query_Cache_Purged',
  'Com_frontend_stmt_execute', 'Com_backend_stmt_execute');

GET counts lookups, GET_OK hits, and SET insertions. For an eligible, warmed lookup, frontend executes and hits increase while corresponding backend executes are avoided. Compare counter changes with backend activity; counters include other traffic, and cache metrics combine binary and text queries.

If repeats miss, check rule order, transaction state, parameter values and types, session settings, and destination hostgroup. Different encodings of equivalent values can use separate entries. A driver’s first execute sending types and a later execute omitting types can need separate warmups. Matching requests can share entries across connections, but text and binary results remain separate.

Prepared results share mysql-query_cache_size_MB with text-query results. Check memory and purges when sizing it; the budget is a soft limit. Optional mysql-query_cache_soft_ttl_pct allows refresh before hard expiry. See the query-cache settings for those controls.

Disable a rule or clear saved results

To stop the example rule selecting requests:

UPDATE mysql_query_rules SET active=0 WHERE rule_id=970101;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Disabling a rule does not remove stored entries. To clear saved answers immediately, including text-query entries, run:

PROXYSQL FLUSH MYSQL QUERY CACHE;

The flush clears the whole MySQL result cache, not one statement or parameter value. Flush when changing backend defaults, permissions, schema semantics, or other context that changes a cached answer’s meaning.

For PostgreSQL applications, see prepared-query result caching.