Skip to content

CONCEPT Cited by 1 source

SET_VAR optimizer hint

Definition

SET_VAR is a MySQL 8.0 optimizer hint that applies a session system variable for the duration of a single query without modifying the underlying connection state. The hint is written in SQL comment syntax: /*+ SET_VAR(var_name = value) */, placed immediately after the SELECT / INSERT / UPDATE / DELETE keyword.

Example (from sources/2026-04-21-planetscale-connection-pooling-in-vitess):

-- Before: tainting SET, then a query
SET unique_checks = 0;
INSERT INTO user (id, name) VALUES (1, 'foo');

-- After: equivalent single-query with SET_VAR hint
INSERT /*+ SET_VAR(unique_checks=0) */ INTO user (id, name) VALUES (1, 'foo');

Both forms produce the same result (unique_checks=0 is in effect while the INSERT runs) but with different connection-state consequences: the SET form taints the connection for all subsequent queries on it, while the SET_VAR form affects only the single query and leaves connection state untouched.

Why it matters to connection pooling

SET_VAR is the per-query escape hatch that lets a pooled connection serve a caller who needs non-default session settings without becoming a tainted connection. Because the setting scope is one query, the connection is fungible again as soon as the query completes — no restore needed, no close needed, no reservation needed.

In Vitess (Era 2, v7.0–v14): when a client issues a SET statement, the proxy does not taint the connection. Instead, it records the setting on the session and rewrites every subsequent query in that session to carry the setting as a SET_VAR hint (Source: sources/2026-04-21-planetscale-connection-pooling-in-vitess):

"Building on the previous example, once Vitess recognizes that the unique_checks setting is being altered, all subsequent queries within that session are rewritten. For example, the query insert into user (id, name) values (1, 'foo') will be rewritten as insert /*+ SET_VAR(unique_checks=0) */ into user (id, name) values (1, 'foo')."

This lets Vitess keep the connection in the shared pool across the caller's session — pool benefits preserved, ORM-compatibility preserved.

Eligibility constraint

Not every system variable can be set via SET_VAR. MySQL's Server System Variables reference marks eligibility per variable. Broadly eligible families:

  • Optimizer-behaviour variables (optimizer_switch, optimizer_trace, optimizer_search_depth).
  • Query-execution variables (unique_checks, foreign_key_checks, sql_mode, max_execution_time).
  • Join/sort tuning (sort_buffer_size, join_buffer_size).

Broadly ineligible families:

  • Variables that affect the session's protocol/encoding state (character_set_client, character_set_results, collation_connection — changing these mid-query would require renegotiation).
  • Variables that affect transaction semantics across statements (autocommit, some aspects of transaction_isolation).
  • Variables that affect session resources (max_user_connections, user-defined variables @x).

"it's essential to note that not all settings can be used with SET_VAR. For those that are not permitted, reserved connections must still be used." — Harshit Gangal, sources/2026-04-21-planetscale-connection-pooling-in-vitess

This means SET_VAR is a partial mitigation, not a complete replacement for connection taint handling. In Vitess's design trajectory, it is one of two pre-v15 mitigations (alongside no-op elision) and remains load-bearing in v15+ alongside the settings pool for settings that are SET_VAR-eligible.

Relationship to the settings pool

Mechanism Setting scope Connection state Pool ceiling
SET var = value Session Tainted Every taint forces reservation
SET_VAR(var=value) Single query Untouched Preserved (for eligible vars only)
Settings pool (v15) Session Tainted, but pool manages it Preserved (all vars, with bookkeeping cost)

The three mechanisms compose. Vitess's current architecture applies SET_VAR for eligible variables (cheaper — no pool bookkeeping) and falls back to the settings pool for ineligible ones (slightly more expensive but covers the full surface).

Historical note

SET_VAR was added in MySQL 8.0.3 (2017). Before MySQL 8.0, the only way to apply a session variable per-query was either (a) SET + query + SET back (two extra round trips), which is incompatible with pooling anyway, or (b) reserve the connection for the whole session. SET_VAR is one of the MySQL 8.0 features Vitess specifically leans on for its v7.0+ pool-compatible-ORM story — another MySQL-8-specific primitive whose availability shapes Vitess's design surface.

Seen in

  • sources/2026-04-21-planetscale-connection-pooling-in-vitess — canonical first wiki citation. Gangal walks through Vitess's pre-v15 query-rewriting mechanism that substitutes SET_VAR hints for client-issued SET statements, keeping connections pool-eligible. The "not all settings can be used with SET_VAR" caveat is canonicalised here as the load-bearing reason why settings-pool (v15) still needed to be built even after SET_VAR rewriting shipped.
Last updated · 347 distilled / 1,201 read