Indexes
Indexes make lookups fast and rules enforceable. The index kind sits in the header — unique, count, fulltext, hnsw, diskann — and each kind brings its own clauses.
The everyday two
A plain index is a lookup; a unique index is a rule:
namespace acme;
database shop;
table message {
schemafull;
field account: string;
field thread_id: string;
field sent_at: datetime;
// Composite lookup for inbox queries.
index by_account_thread {
fields: account, thread_id;
};
index by_recency {
fields: sent_at;
};
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE message TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE account ON message TYPE string;
DEFINE FIELD OVERWRITE thread_id ON message TYPE string;
DEFINE FIELD OVERWRITE sent_at ON message TYPE datetime;
DEFINE INDEX OVERWRITE by_account_thread ON message FIELDS account, thread_id;
DEFINE INDEX OVERWRITE by_recency ON message FIELDS sent_at;
fields: takes an expression list — usually plain field names, comma separated. (SurrealQL's COLUMNS spelling is accepted as an alias.)
namespace acme;
database shop;
table user {
schemafull;
field email: string;
index unique one_email {
fields: email;
concurrently;
};
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE `user` TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE email ON `user` TYPE string;
DEFINE INDEX OVERWRITE one_email ON `user` FIELDS email UNIQUE CONCURRENTLY;
concurrently; builds the index in the background — a build-time directive only; the database doesn't remember it.
Standalone or nested
Every index above nests inside its table. The standalone form carries an on: line instead — handy when you generate fields programmatically, and what the database's report reads back as:
namespace acme;
database shop;
table user {
schemafull;
field email: string;
};
/// No two accounts share an email.
index unique one_email {
on: user;
fields: email;
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE `user` TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE email ON `user` TYPE string;
DEFINE INDEX OVERWRITE one_email ON `user` FIELDS email
UNIQUE
COMMENT 'No two accounts share an email.';
Count: fast totals
A count index maintains a running count — no fields:, optionally filtered:
namespace acme;
database shop;
table reading {
schemafull;
field device: string;
field reading: number;
};
table item {
schemafull;
field status: string;
index count active_count {
where: status = 'active';
};
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE reading TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE device ON reading TYPE string;
DEFINE FIELD OVERWRITE reading ON reading TYPE number;
DEFINE TABLE OVERWRITE item TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE status ON item TYPE string;
DEFINE INDEX OVERWRITE active_count ON item COUNT WHERE status = 'active';
Fulltext: search over text
A fulltext index covers exactly one field, through an analyzer (the default analyzer applies when you omit the rest):
namespace acme;
database shop;
table post {
schemafull;
field title: string;
field body: string;
index fulltext body_fts {
fields: body;
};
index fulltext title_fts {
fields: title;
analyzer: english;
bm25: (1.2, 0.75);
highlights;
};
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE `post` TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE title ON `post` TYPE string;
DEFINE FIELD OVERWRITE body ON `post` TYPE string;
DEFINE INDEX OVERWRITE body_fts ON `post` FIELDS body FULLTEXT;
DEFINE INDEX OVERWRITE title_fts ON `post` FIELDS title
FULLTEXT ANALYZER `english` BM25(1.2,0.75) HIGHLIGHTS;
The pieces: analyzer: names an analyzer; bm25: is the scoring pair; highlights; enables highlighting in query results. At the defaults the clause disappears — index fulltext body_fts { fields: body; }; is the complete minimal form, and the database's verbose default printout folds back to it on normalize. Authoring order between an analyzer and the index that names it does not matter: surform build orders the emitted DDL itself — the analyzer lands before the fulltext index that names it — so the define-time hazard never arises (a true dependency cycle is an error, not an ordering warning).
Vector search: hnsw and diskann
Both vector kinds index exactly one field — an array of numbers — and require dimension::
namespace acme;
database shop;
table embedding {
schemafull;
field vector: array<float>;
index hnsw vec {
fields: vector;
dimension: 768;
distance: cosine;
};
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE embedding TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE vector ON embedding TYPE array<float>;
DEFINE INDEX OVERWRITE vec ON embedding FIELDS vector HNSW DIMENSION 768 DISTANCE COSINE;
Distance — minkowski takes its order in SDL's paren form, and the two pruning flags ride as plain flags:
namespace acme;
database shop;
table embedding {
schemafull;
field vector: array<float>;
index hnsw vec_tuned {
fields: vector;
dimension: 768;
distance: minkowski(3);
type: f16;
m: 16;
efc: 200;
extend_candidates;
keep_pruned_connections;
};
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE embedding TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE vector ON embedding TYPE array<float>;
DEFINE INDEX OVERWRITE vec_tuned ON embedding FIELDS vector
HNSW DIMENSION 768 DISTANCE MINKOWSKI 3 TYPE F16 M 16 EFC 200 EXTEND_CANDIDATES KEEP_PRUNED_CONNECTIONS;
Omitted HNSW parameters derive from m — and when the database reports the derived values back (m0, lm), normalize folds them away again as long as they match.
diskann is the on-disk cousin — different parameter names, a restricted distance/type menu:
namespace acme;
database shop;
table document {
schemafull;
field embedding: array<float>;
index diskann big_vec {
fields: embedding;
dimension: 1536;
distance: cosine_normalized;
type: f16;
degree: 32;
l_build: 50;
alpha: 1.3;
hashed_vector;
};
};DEFINE NAMESPACE OVERWRITE acme;
USE NS acme;
DEFINE DATABASE OVERWRITE shop;
USE NS acme DB shop;
DEFINE TABLE OVERWRITE document TYPE NORMAL SCHEMAFULL;
DEFINE FIELD OVERWRITE embedding ON document TYPE array<float>;
DEFINE INDEX OVERWRITE big_vec ON document FIELDS embedding
DISKANN DIMENSION 1536 DISTANCE COSINE_NORMALIZED TYPE F16 DEGREE 32 L_BUILD 50 ALPHA 1.3 HASHED_VECTOR;
Where next
- Analyzers — tokenizers and filters for fulltext.
- Fields — the fields these indexes cover.
- Schema migration — comparing declared indexes with what
INFO FOR TABLEreports.