Surrealql Performance

SurrealQL Performance

Techniques for making SurrealDB queries fast: structuring record IDs and keys for data locality, choosing and verifying the right indexes, and precomputing values with computed fields and views instead of recomputing them on every read.

Target the latest stable SurrealDB release. Confirm version-sensitive syntax (record ranges, COMPUTED fields, FULLTEXT index options) against https://surrealdb.com/docs, and validate examples with surreal validate. See the surrealql skill for version detection.

When to use this skill

• A query is slow or scans more records than expected
• Designing record IDs to support efficient lookups and range scans
• Choosing between standard, UNIQUE, full-text SEARCH, or vector indexes
• Confirming whether an index is actually used (EXPLAIN)
• Deciding whether to store a derived value vs. compute it on read

Topic map

Top rules

• Design IDs for access patterns. Record IDs are stored in sorted order. Put

the most selective, range-friendly component first (e.g. weather:['London', d'2025-02-13T05:00Z']) so related records sit together and range queries avoid full-table scans. See references/keys.md.

• Prefer record ranges over `WHERE` on the ID. SELECT * FROM person:1..1000

uses key ordering directly; filtering with WHERE after a full scan does not.

• Index the fields you filter, sort, or join on — but no more. Every index

adds write cost. Order composite index fields from most to least selective and to match your query's filter/sort order. See references/indexing.md.

• Verify, don't assume. Run EXPLAIN (or EXPLAIN FULL) to confirm a query

uses the index you expect before concluding it is optimized.

• Precompute expensive, read-heavy values. Use computed fields, a

DEFINE TABLE ... AS SELECT view, or a DEFINE EVENT to maintain derived data rather than recomputing aggregates on every query. See references/computed-fields.md.

• Use bound parameters. Parameterized queries are safer and let the engine

reuse query plans.

Computed & Derived Fields

Recomputing the same derived value on every read is wasteful. SurrealDB offers three ways to compute values once and read them cheaply: stored values (VALUE), computed fields (COMPUTED), precomputed views (DEFINE TABLE ... AS SELECT), and event-maintained denormalized fields (DEFINE EVENT). Choose based on whether the value should be stored on write or recomputed on read.

Computed fields with VALUE

A DEFINE FIELD ... VALUE expression is evaluated and stored whenever the record is written, so reads are plain field reads:

DEFINE FIELD full_name ON TABLE person
  VALUE string::concat(first_name, ' ', last_name);

DEFINE FIELD updated_at ON TABLE person TYPE datetime
  VALUE time::now();

Use this for values derived from the record's own fields.

Computed fields (COMPUTED) — compute on read

A COMPUTED field is evaluated when the record is read, recomputing against the current state each time. Use it when the value depends on related records that change independently and must always be current:

DEFINE FIELD rating ON TABLE product
  COMPUTED { math::mean(SELECT VALUE stars FROM review WHERE product = $parent.id) };

COMPUTED replaces the legacy VALUE <future> { … } form, which is deprecated since SurrealDB 3.0. Trade-off: it moves cost to read time. For hot read paths over expensive computations, prefer a stored value maintained by an event (below).

Precomputed views with DEFINE TABLE ... AS SELECT

A view table stores the result of an aggregation and is maintained incrementally as the underlying records change — ideal for dashboards and rollups that are read far more often than the base data changes:

DEFINE TABLE product_stats AS
  SELECT
    product,
    count() AS reviews,
    math::mean(stars) AS avg_rating
  FROM review
  GROUP BY product;

Query product_stats directly instead of re-aggregating review on every request.

Event-maintained denormalized fields

When you want a stored value (cheap reads) that depends on other records, keep it up to date with a DEFINE EVENT. This denormalizes derived data onto a record at write time:

DEFINE EVENT update_review_count ON TABLE review
  WHEN $event = 'CREATE' OR $event = 'DELETE'
  THEN {
    UPDATE product:[$after.product ?? $before.product] SET
      review_count = count(SELECT id FROM review WHERE product = $after.product);
  };

Choosing an approach

Rule of thumb: read-heavy → store it (VALUE, view, or event); read-rarely but must be live → compute it (COMPUTED).

Indexing

Indexes turn full-table scans into targeted lookups, but each one adds work to every write. Index the fields you filter, sort, or join on — and verify the index is actually used. See the DEFINE INDEX docs.

Index types

-- Standard index on a filter field
DEFINE INDEX idx_email ON TABLE user FIELDS email;

-- Unique constraint (also a fast lookup index)
DEFINE INDEX idx_email_unique ON TABLE user FIELDS email UNIQUE;

-- Full-text search index (FULLTEXT; SEARCH ANALYZER is deprecated since 3.0)
DEFINE INDEX idx_title ON TABLE article
  FIELDS title
  FULLTEXT ANALYZER my_analyzer BM25(1.2, 0.75);

-- Vector index for KNN search
DEFINE INDEX idx_embedding ON TABLE document
  FIELDS embedding
  HNSW DIMENSION 384 DIST COSINE TYPE F32;

For full-text analyzers and BM25 see the surrealql skill's schema reference. For vector index tuning (EFC, M, M0, distance functions) see the surrealdb-vector skill.

Composite indexes and field order

A composite index covers queries that filter on a leading prefix of its fields. Order fields from most to least selective and to match how you query:

-- Supports: filter by status; filter by status + created_at; sort within status
DEFINE INDEX idx_status_created ON TABLE order FIELDS status, created_at;

A query filtering only on created_at (the non-leading field) cannot use this index — define a separate index if that access pattern matters.

Verify index usage with EXPLAIN

Never assume an index is used. Confirm it:

-- Show the query plan and which index (if any) is chosen
SELECT * FROM user WHERE email = 'a@b.com' EXPLAIN;

-- Include actual execution detail
SELECT * FROM user WHERE email = 'a@b.com' EXPLAIN FULL;

Look for an iterator that references your index rather than a full table scan. If the plan scans the table, the index field order, type, or the query shape (e.g. a function wrapped around the field) is preventing index use.

The cost of over-indexing

Every index must be updated on insert, update, and delete. Symptoms of too many indexes: slow writes, large storage footprint, and indexes that EXPLAIN never selects. Keep only indexes that back real query patterns, and remove unused ones:

REMOVE INDEX idx_unused ON TABLE user;

Rebuilding indexes

Rebuild after changing analyzer settings or to recover an index:

REBUILD INDEX idx_title ON TABLE article;

Checklist

1. Identify the exact filter, sort, and join fields per query. 2. Define one index per access pattern; order composite fields to match. 3. Run EXPLAIN to confirm the index is selected. 4. Remove indexes that no query plan uses.

Record ID & Key Structuring

Record IDs in SurrealDB are not just identifiers — they are the primary physical key, stored in sorted order. Designing IDs around your access patterns is the single highest-leverage performance decision, because it controls data locality and whether range queries can avoid full-table scans. See the Record IDs docs.

A record ID is table:identifier

person:surrealdb          -- string identifier
person:17493              -- integer identifier (64-bit)
person:⟨complex id⟩       -- use ⟨…⟩ (or backticks) to escape unusual identifiers

IDs sort in a natural order, so records that share a prefix are stored near each other. Lookups by full ID are direct key reads — the cheapest possible access.

ID generation strategies

Time-sortable IDs (ULID, UUID v7) keep recently-created records together, which helps time-range scans and pagination. Purely random IDs spread writes out, avoiding write hot spots but giving no useful range locality.

Compound (array) IDs for locality and range scans

Array IDs are the key tool for high-performance range queries. Order components from coarsest to finest so the prefix you filter on comes first:

-- Group readings by city, then time: all London readings sit together,
-- ordered by timestamp.
CREATE weather:['London', d'2025-02-13T05:00:00Z'] SET temperature = 5.7;
CREATE weather:['London', d'2025-02-13T06:00:00Z'] SET temperature = 6.1;

Record ranges

Range queries use ID ordering directly instead of scanning and filtering:

-- Inclusive numeric range
SELECT * FROM person:1..=1000;

-- All London readings within a time window (prefix + sub-range)
SELECT * FROM weather:['London', d'2025-02-13T00:00:00Z']
                  ..['London', d'2025-02-14T00:00:00Z'];

-- Open-ended bounds
SELECT * FROM person:1000..;
SELECT * FROM person:..=1000;

Prefer a record range over SELECT ... WHERE id > …: the range walks the sorted key space, while a WHERE filter on a scanned set does not benefit from ID ordering.

Avoiding hot keys

Monotonic IDs concentrate every new write at the "end" of the key space, which can become a write bottleneck under heavy ingest. Trade-off:

• Need range/time locality → ULID / UUID v7 / time-prefixed compound IDs.
• Need maximum write throughput, no range needs → rand(), or put a

higher-cardinality component (e.g. a shard or tenant id) first in a compound ID to spread writes.

Sequences for monotonic counters

When you need a strict, gap-aware incrementing number (invoice numbers, order numbers), use a sequence rather than counting rows:

DEFINE SEQUENCE invoice_number;

CREATE invoice SET number = sequence::nextval('invoice_number');

Sequences are designed for concurrent use and avoid the race conditions of SELECT count()-based numbering.