Why Cache at the Proxy Layer?
Application-level caches (Redis, Memcached) are powerful but require code changes: the developer must add cache-check logic, handle cache invalidation, and manage TTLs explicitly. A proxy cache is transparent — the application sends a SQL query and gets a result back, with no knowledge that the result came from cache rather than the database.
MySQL's built-in Query Cache was removed in MySQL 8.0 because it used a global mutex that serialized all writes and cache invalidations, making it a bottleneck under write-heavy workloads. Caching before the query reaches the database avoids this problem entirely.
How ProxySQL Query Caching Works
ProxySQL allows you to define caching rules using regular expressions matched against incoming query text. Each rule specifies a TTL (time-to-live) in milliseconds — for example, a rule matching product-lookup queries with a 60-second cache_ttl, inserted into mysql_query_rules via the admin interface and applied live with LOAD MYSQL QUERY RULES TO RUNTIME (no restart needed).
When a query matches this rule:
- First execution: ProxySQL forwards the query to the database, receives the result set, stores it in its in-memory cache keyed by the normalized query text, and returns the result to the application.
- Subsequent executions (within 60 seconds): ProxySQL matches the rule, finds the cached result, and returns it immediately. The query never reaches the database server.
The cache key is the normalized query text — parameter values are included, so SELECT ... WHERE id = 1 and SELECT ... WHERE id = 2 are cached separately.
Limitations to Know
- ProxySQL's cache is in-memory only and is lost on restart.
- There is no active invalidation — if a product's price changes, the old cached result will be served until the TTL expires. This makes it suitable for data that is read-heavy and tolerates slight staleness (product catalog, configuration tables), not for data that must be immediately consistent (account balances, inventory counts).
- Cache TTL should be tuned to the acceptable staleness window for each query type.
A common interview scenario: "Your team wants to reduce database load without changing application code. What are your options?" The proxy query cache is the strongest answer here — it is transparent to the application, requires no code changes, and can be deployed and tuned independently. Contrast this with Redis (requires code changes) or read replicas (reduces write load but not read query frequency). Be ready to discuss the staleness trade-off and why it makes the cache unsuitable for transactional data.