Query optimisation

This page contains a number of tips you can use to optimise your queries in SurrealDB. For more details on each pattern, see the linked pages in the API documentation.

Inspecting the plan with EXPLAIN

Prefix a read-only statement with EXPLAIN to see how the database plans to run it. Add ANALYZE if you want timing and row metrics as well. As the output of this statement is informational and may change between versions, be sure not to build tooling that depends on an exact shape.

EXPLAIN SELECT * FROM person WHERE email = 'user@example.com';

Full syntax and options can be found in the EXPLAIN reference.

Record ranges

When you can identify records by record ID order (for example numeric or time-ordered IDs), selecting with a range on the ID (table:start..end) avoids scanning the whole table. A WHERE filter over the same records can be much more expensive because it typically implies a wider scan.

SELECT * FROM person:1..1000;

See record IDs and record ranges in SELECT.

Async events

By default, events run in the same transaction as the write that triggers them, which keeps behaviour easy to reason about but can slow commits if event logic is heavy.

Using the ASYNC clause in a DEFINE EVENT statement runs the handler after the triggering transaction. This leads to lower write latency, with the caveat that the handler opts out of the ACID guarantees that apply by default to all transactions. As such, it should only be used when this tradeoff is acceptable.

More context: Reactive patterns.

Pre-allocated fields (denormalised flags)

If a query repeatedly does a lookup or subquery only to answer a yes/no question (“is this user registered?”), consider storing the answer in a field. For example, an is_registered field updated when the user completes registration is more efficiently written ahead of time as a boolean value as opposed to using an extra SELECT inside another query.

This will still need a strategy to keep the flag up to date, but allows you to avoid paying the check cost on every read.

Indexes and iterators

• •

  Define indexes that match real filter and sort patterns; see DEFINE INDEX.

• •

  The WITH clause can force or restrict which index the planner uses when you need predictable behaviour (for example comparing plans with EXPLAIN).

How SurrealDB combines indexes

Available since: v3.3.0

When a WHERE clause holds several conditions that indexes can answer, SurrealDB can use all of those indexes together rather than choosing one and checking the other conditions record by record. Each record in a table has a numeric document ID that every index on the table shares. Reading an index for one condition gives a bitmap of the IDs that match it, and bitmaps combine with set operations that read no records. Only the records left after the last operation are fetched, and the full WHERE clause is checked again on each of them, as are the table and field permissions.

The conditions combine as follows:

• •

  AND intersects the bitmaps, starting from the index with the most selective condition.

• •

  OR, at the top level or inside an AND, can become a union when every branch has an index, so a record that matches several branches is fetched once.

• •

  AND NOT subtracts one bitmap from another. The subtracted condition must be a full-text @@ match, or be on a field whose declared type cannot hold an array, such as TYPE string or TYPE bool. An array value has one index entry per element, so its bitmap would remove records the condition does not match.

• •

  A single-hop graph condition such as ->wrote->post CONTAINS post:one becomes a bitmap of the records connected to that record, and is intersected with the index bitmaps. It needs at least one index-backed condition beside it.

• •

  A count() with GROUP ALL is the size of the final bitmap when every condition is represented exactly: an indexed field with a declared type that cannot hold an array, or a full-text @@ match. The count then reads no records.

DEFINE FIELD category ON article TYPE string;
DEFINE FIELD published ON article TYPE bool;
DEFINE INDEX idx_category ON article FIELDS category;
DEFINE INDEX idx_published ON article FIELDS published;

EXPLAIN SELECT count() FROM article WHERE category = 'databases' AND published = true GROUP ALL;

"IndexCountScan [ctx: Db] [source: article, condition: category = 'databases' AND published = true]
    BitmapAnd [ctx: Db]
        BitmapIndexScan [ctx: Db] [index: idx_category, access: = 'databases']
        BitmapIndexScan [ctx: Db] [index: idx_published, access: = true]
"

The planner keeps its other plans where they are faster: when an index already returns the records in the ORDER BY order, including ORDER BY id, when a LIMIT has no ORDER BY so that the scan can stop early, and when the statement has a WITH clause or a VERSION clause.

Index reads are bounded by SURREAL_BITMAP_BRANCH_BUDGET, 250,000 entries by default, where 0 removes the limit. In an AND, the first index is always read in full. A later branch that reads more than the limit, or more than 64 times the number of records left so far, is dropped, and its condition is checked on each fetched record instead. In a top-level OR, a branch that passes the limit makes the query fall back to reading the indexes separately. The limit keeps a condition that matches most of the table from making the combined plan slower than the plan it replaced. See environment variables.

The same bitmaps also pre-filter vector searches. The plan operators are listed under bitmap index fusion in the EXPLAIN reference.

Fast table counts

For SELECT count() … GROUP ALL over a whole table, a COUNT index maintains a running total instead of scanning every record each time. See the note under SELECT - COUNT index.