Query Caching

Query Caching: Architectural Deep-Dive

The Problem

Many applications issue the same queries repeatedly. Configuration lookups, reference data, aggregate summaries, session-scoped reads — these queries return identical results across thousands of requests, yet each one travels the full path to the database (for example with MySQL), acquires a connection, executes against storage, and returns. At scale, this redundancy becomes measurable: unnecessary CPU on the database server, connection pressure, and latency that accumulates in every user-facing response.

The conventional fix is application-layer caching — Redis, Memcached, or in-process caches built into the ORM or service layer. These work, but they require explicit implementation in every service that benefits, introduce a separate infrastructure dependency, and create invalidation logic that must be maintained alongside the application. Cached data becomes an application concern distributed across every team that touches the codebase.

The ProxySQL Approach

ProxySQL’s query cache operates at the wire protocol level, between the application and database. When a query matches a caching rule, ProxySQL checks its in-memory cache before forwarding to a backend. If a valid cached result exists, it is returned immediately — no connection to the database is acquired, no query is executed, and the response time is bounded only by memory access latency.

Cache rules are defined using the same query rule engine that drives ProxySQL’s routing and rewriting capabilities. A rule specifies which queries to cache using regex pattern matching, and attaches a TTL controlling how long the cached result is valid. When the TTL expires, the next matching query is forwarded to the database, the result is refreshed in the cache, and subsequent requests are served from the updated entry. The entire cycle is transparent to the application.

What You Can Do With It

Reference Data and Configuration Lookups are the highest-value caching targets. Queries that return data changing on the order of minutes or hours — product catalogues, feature flags, permission tables, geographic data — can be cached with long TTLs and served from memory across millions of requests without a single backend round-trip.

Aggregate and Reporting Queries that are expensive to compute but tolerate some staleness are natural candidates. Dashboard metrics, leaderboard counts, inventory summaries — caching these at the proxy layer removes their cost from the database’s hot path entirely, freeing execution capacity for latency-sensitive transactional queries.

Traffic Spike Absorption is a practical operational benefit. When a specific query pattern drives a sudden surge in database load — a viral page, a scheduled job fan-out, a traffic spike after a deployment — a cache rule can be added at runtime to absorb the redundant load without a code change or redeployment. ProxySQL’s admin interface allows cache rules to be created, modified, and removed live.

Selective Caching with Exclusions allows fine-grained control. Write queries, queries involving session variables, or queries returning user-specific data can be explicitly excluded from caching via rule ordering, while read-heavy, shared-result queries are cached aggressively. The rule engine evaluates in priority order, so exclusions and inclusions compose predictably.

The Result

Query caching at the proxy layer reduces database server load, lowers response latency for high-frequency read patterns, and absorbs traffic spikes without application changes or additional infrastructure dependencies. The cache lives in ProxySQL — the component already sitting in the query path — so there is no separate cache tier to operate, no client library integration to maintain, and no invalidation logic to implement in application code. High-cost, frequently repeated queries stop reaching the database. The database handles what only the database can handle.