Vindex Selection and Tuning: Matching Vindex Types to Access Patterns in a Sharded Keyspace

The vindex is the single function that decides which shard a row lives on, and every query’s cost is downstream of that choice. Pick a primary vindex with too little entropy and one shard becomes a write hotspot while the others idle; add a lookup vindex without accounting for its write amplification and every INSERT quietly becomes a two-shard transaction. This guide is for database platform engineers and MySQL SREs who are past “it routes” and now need the vindex layout to hold up under production write volume and tail-latency SLOs. It sits within VSchema configuration and routing-rule management and assumes you already know how the physical shard ranges are laid out from understanding Vitess keyspace partitioning models; the concern here is narrower — given a set of query access patterns, which vindex type serves each one at the lowest steady-state cost.

Prerequisites #

Confirm the following before you change any vindex definition on a live keyspace:

• Vitess 16+, so that consistent_lookup and consistent_lookup_unique are available and the CreateLookupVindex / ExternalizeVindex workflow is stable. The examples assume 19 or newer.
• A valid, versioned VSchema already applied to the keyspace. The grammar of vindex, table, and sequence definitions is covered in mastering VSchema syntax and structure; this page changes which vindex a column uses, not how the document is written.
• vtexplain reachable locally with a copy of the keyspace’s VSchema and CREATE TABLE statements. Every vindex change must be plan-tested offline before it ships.
• Baseline cardinality figures for each candidate sharding column: COUNT(DISTINCT col) and the count of the single most frequent value. A column can have high cardinality overall and still hotspot if one value dominates.
• Assumed knowledge: the difference between a targeted single-shard query and a scatter, and why a column that is not the primary vindex needs a secondary lookup vindex to stay targeted.

Choosing by Access Pattern #

The decision is not “which vindex is best” but “which vindex is cheapest for the way this table is actually queried.” Map each table’s dominant access pattern to a vindex family first, then tune within it. The matrix below is the starting point; the cost columns in the reference table later refine it.

Two families sit behind that matrix. Functional vindexes compute the keyspace ID directly from the column value with a pure function — no extra table, no extra write. hash and xxhash scatter values uniformly; numeric is the identity map (the column value is the keyspace ID); unicode_loose_md5 case-folds and normalizes a string before hashing so 'Jane' and 'jane' land together. Lookup vindexes — lookup, lookup_unique, consistent_lookup, consistent_lookup_unique — store a from → to mapping in a backing table, so they can route by a column that is not the sharding key, at the cost of maintaining that table on every write.

Primary Versus Secondary Vindexes #

Every table in a sharded keyspace has exactly one primary vindex and zero or more secondary vindexes, and the two roles are not interchangeable. The primary vindex is the first entry in a table’s column_vindexes list, and it alone determines physical placement — the shard a row is written to is a function of the primary vindex applied to its column value. Because placement must be deterministic and reversible-free, a primary vindex must be functional (no backing table to consult on the write path) and unique (one keyspace ID per row). That rules out lookup and the non-unique variants as primary vindexes: you cannot place a row by reading a table that does not yet contain it.

A secondary vindex, by contrast, does not move rows. It exists so that a query filtering on a different column can still be routed to a single shard instead of scattering. Secondary vindexes are where lookup vindexes earn their keep: order_id is not the sharding key, but a lookup_unique on it lets VTGate translate an order_id predicate into the shard that holds the row. You can attach several secondary vindexes to one table — one per column you routinely query by — but each adds maintenance cost on writes, so the count should track real access patterns, not every column that might be filtered.

The practical consequence is an ordering rule when you write the column_vindexes array: the placement column comes first, and everything after it is an alternate routing path. Reordering that list is not cosmetic — promoting a different vindex to first position changes where rows are placed, which is a data move, not a config edit.

Core Mechanism: How VTGate Turns a Vindex Into a Shard #

When VTGate plans a query, it looks at the WHERE clause for a predicate on a column that carries a vindex. If it finds one, it invokes that vindex to produce a keyspace ID — an opaque byte string — then finds the shard whose range contains it.

For a functional vindex the computation is local and synchronous. hash runs the column value through a 3DES-based hash to yield an 8-byte keyspace ID; xxhash uses the faster non-cryptographic xxHash64. Either way the byte string is compared against the shard ranges: keyspace ID 0x6b... falls in -80, 0xa1... falls in 80-. No table is read, so the mapping costs nothing beyond CPU and never becomes inconsistent.

For a lookup vindex the “computation” is a query. The vindex reads its backing table — SELECT to_col FROM lookup_table WHERE from_col = ? — to translate the queried column into the keyspace ID (or, for the unique variants, directly into the destination). That extra hop is what lets you target a shard by order_id when the table is sharded by customer_id, but it means the lookup table must be kept in step with the base table on every write.

The distinction that governs write cost is ownership. A lookup vindex marked owner on a table means Vitess maintains the mapping automatically: inserting a row inserts the lookup entry, deleting removes it. An unowned lookup vindex is read-only from Vitess’s perspective — you promised to populate it yourself, and if you drift, routing silently returns wrong or empty results. The primary vindex, by contrast, is always the first column_vindex on a table and must be a functional, unique vindex, because it is what actually places the row.

{
  "sharded": true,
  "vindexes": {
    "hash": { "type": "hash" },
    "order_id_lookup": {
      "type": "lookup_unique",
      "params": {
        "table": "commerce.order_id_lookup",
        "from": "order_id",
        "to": "keyspace_id"
      },
      "owner": "orders"
    }
  },
  "tables": {
    "orders": {
      "column_vindexes": [
        { "column": "customer_id", "name": "hash" },
        { "column": "order_id", "name": "order_id_lookup" }
      ]
    }
  }
}

Here customer_id is the primary vindex (row placement); order_id is a secondary lookup vindex owned by orders, so a query filtering on either column stays single-shard. The first entry always places the row; every later entry is an alternate routing path Vitess maintains for you.

Cardinality, Entropy, and the Cost of a Bad Primary Vindex #

A primary vindex is only as good as the distribution of the column feeding it. Two properties matter and they are not the same. Cardinality is the number of distinct values — a customer_id with ten million distinct values has high cardinality. Entropy is how evenly rows spread across those values — if 40% of orders belong to one wholesale account, cardinality is high but entropy is low, and a hash of customer_id still concentrates 40% of writes on whichever shard owns that account’s keyspace ID.

Functional hashing (hash, xxhash) fixes skew across distinct values by scattering them uniformly, but it cannot fix skew within a value: every row with the same customer_id hashes to the same shard by design, which is exactly what co-locates a customer’s data. So the rule is: choose a primary vindex column whose most frequent value is a small fraction of total rows. When no such column exists, a composite or synthetic key is usually the answer — never a low-cardinality column like status or region.

A quick way to reason about it: entropy sets a floor on how evenly writes can spread, and the hash function decides how close you get to that floor. A perfectly uniform hash over a column where one value owns 30% of rows still sends 30% of writes to one shard, because the hash is deterministic per value. No vindex tuning recovers entropy the key does not have — that is why key selection precedes vindex selection, and why a low-entropy key is a resharding problem dressed up as a routing problem.

Cost Trade-offs: Write Amplification and Cross-Shard Lookups #

Every secondary vindex you add buys targeted reads on one more column and pays for them on every write. The two dominant costs are write amplification and cross-shard lookups, and they pull in opposite directions from the read benefit.

Write amplification is the extra work each write incurs to keep the mapping current. An owned lookup_unique turns one base insert into an insert on the base shard plus an insert on the lookup shard, and because those shards usually differ, the pair commits as a two-phase transaction — roughly doubling write latency and adding 2PC coordinator load. Three secondary lookup vindexes on a hot table means three extra maintained mappings, so a single logical insert can fan into four coordinated writes. That is fine on a table written a hundred times a second and ruinous on one written ten thousand times a second; the throughput ceiling is set by the 2PC path, not the base shard.

Cross-shard lookups are the read-side cost of not having the right vindex. A query filtering on a column with no vindex scatters to every shard, so its latency is bounded by the slowest shard and its cost grows with shard count. The whole point of a secondary vindex is to convert that scatter into a single-shard route — but a lookup vindex itself performs a read on its backing table first, so even the “targeted” path is two hops (read the mapping, then read the row). The design question is therefore always comparative: is the scatter you are removing more expensive than the write amplification you are adding. For a column queried on the hot read path and written rarely, a lookup vindex is a clear win; for a column written constantly and queried rarely, the amplification outweighs the occasional scatter and you are better off letting that query scatter or serving it from an analytics replica.

Adding or Changing a Vindex in VSchema #

Changing a primary vindex reshuffles physical row placement and is effectively a reshard; changing or adding a secondary vindex is a VSchema operation plus a backfill. The steps below add a secondary lookup vindex without downtime.

1. Create the backing lookup table in an unsharded keyspace (or a keyspace sharded by the lookup’s from column). It holds the mapping only:

   CREATE TABLE commerce.order_id_lookup (
     order_id     BIGINT UNSIGNED NOT NULL,
     keyspace_id  VARBINARY(10)   NOT NULL,
     PRIMARY KEY (order_id)
   );
   

2. Let Vitess build and backfill it rather than hand-writing the VSchema and populating rows yourself. CreateLookupVindex adds the vindex definition in write_only mode and starts a VReplication stream that reads the base table and fills the lookup table:

   vtctldclient CreateLookupVindex --tablet-types=PRIMARY commerce \
     --vindex '{
       "sharded": true,
       "vindexes": {
         "order_id_lookup": {
           "type": "lookup_unique",
           "params": {
             "table": "commerce.order_id_lookup",
             "from": "order_id",
             "to": "keyspace_id"
           },
           "owner": "orders"
         }
       },
       "tables": {
         "orders": {
           "column_vindexes": [
             { "column": "order_id", "name": "order_id_lookup" }
           ]
         }
       }
     }'
   

3. Wait for the backfill to catch up, then externalize it. Externalizing flips the vindex out of write_only so VTGate starts reading it for routing, not just writing to it:

   vtctldclient ExternalizeVindex commerce.order_id_lookup
   

4. Verify the plan changed with vtexplain (see Verification) — a query on order_id must now show a single-shard Route rather than a scatter.

For a functional secondary vindex (say adding unicode_loose_md5 on username for a table that also carries a hash primary vindex) there is no backfill: you edit the VSchema, apply it, and VTGate recomputes plans immediately, because a functional vindex needs no table.

Vindex Reference #

Cost below is steady-state per write and per read for a table on which the vindex is the primary routing path unless noted.

The functional/lookup split is the primary cost boundary; the lookup versus consistent_lookup split is the second, and it is examined on its own in consistent lookup vs lookup vindex. The choice between uniform hashing and ordered range distribution for the primary vindex — the one that decides physical placement — is worked through in range vs hash vindex selection.

Failure Modes #

Low-cardinality primary vindex hotspot. Symptom: one shard’s VTTablet shows sustained high write QPS and replication lag while sibling shards idle; vtgate_queries_processed skews to one shard’s targets. Root cause: the primary vindex column has low entropy — a dominant value (a bulk account, a default tenant_id) hashes all its rows to one shard. Mitigation: a primary vindex cannot be changed in place without a reshard; move to a higher-entropy or composite key and re-place data via a Reshard/MoveTables workflow. Confirm with COUNT(*) GROUP BY <most-frequent-value> before committing.

Unowned lookup drift. Symptom: a query on the secondary column returns fewer rows than exist, or routes to the wrong shard; vtexplain shows a single-shard route but the row is elsewhere. Root cause: the lookup vindex is not marked owner, so Vitess does not maintain it — inserts to the base table never updated the mapping and it has drifted. Mitigation: make the vindex owned (so maintenance is automatic), or rebuild the mapping table from the base table and reconcile; never leave a routing-critical lookup unowned.

2PC amplification under write burst. Symptom: write latency doubles and commit errors rise the moment an owned lookup/lookup_unique is externalized. Root cause: every insert now spans the base shard and the lookup shard as a two-phase commit; a write burst multiplies the distributed-transaction load. Mitigation: switch the vindex to consistent_lookup, which updates the mapping without 2PC, accepting a small eventual-consistency window on the secondary read path.

Scatter after a “no-op” VSchema edit. Symptom: a query that used to be single-shard suddenly scatters after a VSchema change that “only renamed a vindex.” Root cause: the column’s column_vindexes entry now names a vindex that no longer exists or is write_only, so VTGate falls back to scatter. Mitigation: re-run vtexplain on every affected query as part of the change; a vindex that is still write_only (not yet externalized) is not used for reads.

Verification #

Never trust a vindex change until the plan proves it. vtexplain computes the routing plan offline against the VSchema and schema, with no live tablet involved:

# A predicate on order_id must now resolve to ONE shard, not scatter
vtexplain --vschema-file vschema.json --schema-file schema.sql \
  --shards 4 --sql "SELECT * FROM orders WHERE order_id = 90218"

A correct result shows a single Route with Sharded opcode SelectEqualUnique targeting one shard. If it prints SelectScatter, the lookup is not being used for reads — usually because it is still write_only or the column name in column_vindexes does not match the query predicate. For the primary vindex, spot-check entropy directly against a tablet:

# The most frequent sharding-key value should be a small fraction of rows
vtctldclient ExecuteFetchAsDBA <primary-tablet-alias> \
  "SELECT customer_id, COUNT(*) c FROM orders GROUP BY customer_id ORDER BY c DESC LIMIT 5"

If the top value is a large share of the table, the hashing is fine but the key is the hotspot, and no vindex tuning will fix a placement decision — that is a resharding conversation.

Related #

• Range vs Hash Vindex Selection — uniform scatter versus ordered range locality for the primary vindex, and the hotspot trade-off.
• Consistent Lookup vs Lookup Vindex — 2PC-backed lookups versus the eventual-consistency consistent_lookup variant.
• Configuring Lookup Vindexes for Cross-Shard Joins — using a secondary vindex to keep a join targeted instead of scattering.
• Mastering VSchema Syntax and Structure — the full grammar the vindex definitions above are written in.
• Understanding Vitess Keyspace Partitioning Models — how shard ranges consume the keyspace IDs a vindex produces.

← Back to VSchema Configuration & Routing-Rule Management · Related area: Vitess Sharding Architecture & Topology Design