Configuration & SQL Support
Hint-Based Caches

Hint-based caches let you create shallow caches inline, directly within your application's SQL queries. Instead of managing caches out-of-band through a Readyset shell or admin connection, you embed a special comment (a hint) in a SELECT statement and Readyset automatically creates the cache on first execution.

You can also use hints to bypass caching entirely for individual queries with SKIP CACHE, routing them straight to the upstream database.

Why use hints?

With DDL-based cache creation (CREATE SHALLOW CACHE ... FROM ...), a DBA or operations engineer creates caches ahead of time through the Readyset shell. This works well for a known, stable set of queries but can be cumbersome when:

  • Application teams own query performance. Developers know which queries are latency-sensitive and can annotate them at the source, right next to the query itself, without requiring a separate operational step.
  • Caches should be created on demand. New queries or code paths are deployed frequently, and you want caching to follow the code rather than requiring manual DDL after each deploy.
  • Different queries need different policies. Each hint can carry its own TTL, refresh, and coalesce settings, so the application can express per-query caching requirements directly in the SQL text.

Hints give your application declarative control over what gets cached and how, making cache management part of your code review and deployment workflow rather than an external operational concern.

Syntax

Place a hint comment immediately after the SELECT keyword:

SELECT /*rs+ CREATE SHALLOW CACHE [options] */ columns FROM ...
SELECT /*rs+ SKIP CACHE */ columns FROM ...

The hint uses the optimizer-hint comment format /*rs+ ... */. The rs prefix identifies it as a Readyset directive and is case-insensitive (/*rs+, /*RS+, and /*Rs+ are all equivalent).

CREATE SHALLOW CACHE syntax

SELECT /*rs+ CREATE SHALLOW CACHE
    [POLICY TTL <n> SECONDS [REFRESH [EVERY] <n> SECONDS]]
    [COALESCE <n> SECONDS]
    [ALWAYS]
*/ ...

The options are identical to those of the CREATE SHALLOW CACHE DDL statement. See the Shallow Cache Reference for a detailed description of each option.

When no options are specified, the cache uses the server defaults.

SKIP CACHE syntax

SELECT /*rs+ SKIP CACHE */ ...

SKIP CACHE takes no options. See Skipping the cache for details and use cases.

How it works

  1. First execution — Readyset parses the hint, strips it from the query, and creates a shallow cache for the underlying SELECT. The result is returned from the upstream database (cache miss).

  2. Subsequent executions — The same query (with or without the hint) matches the existing shallow cache and is served directly from it (cache hit).

  3. Stable query identity — The hint is stripped before computing the query ID hash. This means a hinted query and the same query without the hint produce the same query ID and share the same cache entry. For example:

    -- These two queries share the same cache:
SELECT /*rs+ CREATE SHALLOW CACHE */ id FROM users WHERE id = 1;
SELECT id FROM users WHERE id = 1;

  4. Persistence — Hint-created caches are persisted exactly like DDL-created caches. If Readyset restarts, they are automatically restored with their original policy settings.

Examples

Basic cache creation

-- First execution: creates the cache, returns from upstream
SELECT /*rs+ CREATE SHALLOW CACHE */ id, name
FROM users
WHERE id = 1;
 
-- Second execution: served from the shallow cache
SELECT id, name
FROM users
WHERE id = 1;

With Explicit TTL eviction

SELECT /*rs+ CREATE SHALLOW CACHE POLICY TTL 300 SECONDS */ *
FROM products
WHERE category = 'electronics';

Cached entries expire after 300 seconds. See TTL expiration for details on how TTL works.

With TTL and scheduled refresh

SELECT /*rs+ CREATE SHALLOW CACHE POLICY TTL 300 SECONDS REFRESH EVERY 60 SECONDS */ *
FROM products
WHERE category = 'electronics';

Entries are proactively refreshed every 60 seconds. See Refresh behavior for the difference between on-demand and scheduled refresh.

With coalesce window

SELECT /*rs+ CREATE SHALLOW CACHE COALESCE 10 SECONDS */ *
FROM dashboard_stats
WHERE tenant_id = 42;

Multiple cache misses arriving within a 10-second window are coalesced into a single upstream query.

With ALWAYS (transaction support)

SELECT /*rs+ CREATE SHALLOW CACHE ALWAYS */ balance
FROM accounts
WHERE account_id = 100;

The cache is consulted even when the query is executed inside a transaction (e.g., by an ORM that wraps reads in BEGIN/COMMIT).

Prepared statements

Hints work with prepared statements via the binary protocol. The cache is created during the first prepare + execute cycle. Place the hint in the query string passed to the driver's prepare method:

import mysql.connector
 
conn = mysql.connector.connect(host="127.0.0.1", port=3307, database="mydb")
cursor = conn.cursor(prepared=True)
 
# The hint is embedded in the prepared query string.
# On first execute, Readyset creates the shallow cache.
cursor.execute(
    "SELECT /*rs+ CREATE SHALLOW CACHE */ id, val FROM t WHERE id = %s",
    (1,),
)
row = cursor.fetchone()

Supported query shapes

Hints work with a variety of query structures:

  • Simple SELECTs — SELECT /*rs+ CREATE SHALLOW CACHE */ ... FROM t
  • UNION queries — SELECT /*rs+ CREATE SHALLOW CACHE */ ... UNION SELECT ...
  • CTEs — WITH cte AS (...) SELECT /*rs+ CREATE SHALLOW CACHE */ * FROM cte
  • Window functions — SELECT /*rs+ CREATE SHALLOW CACHE */ ROW_NUMBER() OVER (...), ...
  • Derived tables — SELECT /*rs+ CREATE SHALLOW CACHE */ * FROM (SELECT ...) AS sub

The hint can appear in any SELECT within the query (e.g., on the left or right branch of a UNION), though placing it on the outermost SELECT is the recommended convention, because the entire query is cached, regardless of where the hint appears.

Skipping the cache

The SKIP CACHE hint forces a query to bypass Readyset entirely and execute directly against the upstream database, even when a matching cache exists.

SELECT /*rs+ SKIP CACHE */ columns FROM ...

When to use SKIP CACHE

  • Reading your own writes. After an INSERT or UPDATE, the next read may need the freshest possible data before the cache has been refreshed. SKIP CACHE guarantees you read from the source of truth.
  • Consistency-sensitive operations. Financial transactions, inventory checks, or any workflow where serving a stale value could cause incorrect behavior.
  • Debugging and validation. Compare cached results against upstream to verify cache correctness during development or incident triage.
  • Selective opt-out. Most queries in a code path can benefit from caching, but one or two need to hit upstream. SKIP CACHE lets you opt out per-query without changing routing rules or cache configuration.

How it works

  1. Readyset parses the SKIP CACHE hint and strips it from the query.
  2. The query is routed directly to the upstream database, bypassing both shallow and deep caches.
  3. If a matching cache exists, Readyset still computes the query ID so that the skip is visible in metrics, but the cache is never consulted.

Example

-- This query always goes to upstream, even if a cache exists
SELECT /*rs+ SKIP CACHE */ balance
FROM accounts
WHERE account_id = 100;
 
-- The same query without the hint still uses the cache
SELECT balance
FROM accounts
WHERE account_id = 100;

Combining with CREATE CACHE hints

SKIP CACHE and CREATE CACHE are independent directives. A query can have a cache created via a CREATE SHALLOW CACHE hint on one code path, while a different code path uses SKIP CACHE to bypass it:

-- Code path A: creates and uses the cache
SELECT /*rs+ CREATE SHALLOW CACHE POLICY TTL 60 SECONDS */ balance
FROM accounts WHERE account_id = ?;
 
-- Code path B: bypasses the cache for a fresh read
SELECT /*rs+ SKIP CACHE */ balance
FROM accounts WHERE account_id = ?;

Behavior details

Malformed hints

If the hint text is syntactically invalid (e.g., POLICY TT instead of POLICY TTL), Readyset logs a warning and ignores the directive. The query still executes normally and if a matching cache already exists, it is used.

Multiple hints

If a query contains multiple optimizer hints (e.g., /*rs+ CREATE SHALLOW CACHE */ /*mysql+ SET_VAR(...) */), all hints are stripped from the query before computing the query ID. Only the first rs-prefixed hint is interpreted as a directive. This ensures the query ID remains stable regardless of how many hints are present.

Cache DDL disabled

If the Readyset server is running with a dedicated --cache-ddl-address, hints arriving on the main listener are silently ignored and no cache is created. The query executes normally against upstream.

Managing hint-created caches

Caches created via hints are identical to caches created via DDL. You can inspect them with SHOW CACHES and remove them with DROP CACHE:

SHOW CACHES;
DROP CACHE <query_id>;
Shallow CachingConfiguration Options