DEFINE INDEX

DEFINE INDEX statement

Just like in other databases, SurrealDB uses indexes to help optimize query performance. An index can consist of one or more fields in a table and can enforce a uniqueness constraint. If you don't intend for your index to have a uniqueness constraint, then the fields you select for your index should have a high degree of cardinality, meaning that there is a high amount of diversity between the data in the indexed table records.

Requirements

• You must be authenticated as a root or namespace user before you can use the DEFINE INDEX statement.
• You must select your namespace and database before you can use the DEFINE INDEX statement.

Statement syntax

SurrealQL Syntax

DEFINE INDEX @name ON [ TABLE ] @table [ FIELDS | COLUMNS ] @fields
    [ UNIQUE | SEARCH ANALYZER @analyzer [ BM25 [(@k1, @b)] ] [ HIGHLIGHTS ] ]

Example usage

How to create a unique index for the email address field on a user table.

-- Make sure that email addresses in the user table are always unique
DEFINE INDEX userEmailIndex ON TABLE user COLUMNS email UNIQUE;

How to create a non-unique index for an age field on a user table.

-- optimise queries looking for users of a given age
DEFINE INDEX userAgeIndex ON TABLE user COLUMNS age;

How to create a full-text search index for a name field on a user table.

-- Allow full-text search queries on the name of the user
DEFINE INDEX userNameIndex ON TABLE user COLUMNS name SEARCH ANALYZER ascii BM25 HIGHLIGHTS;