Overview

Indexing, or property indexing, is a technique used in Ultipa to speed up query performance by creating indexes on specific properties of nodes and edges. This allows the database to quickly locate and retrieve data based on indexed properties. Indexes are particularly beneficial for large datasets, as they optimize the filtering of specific property values without needing to scan the entire graph.

Index Types

Ultipa supports single index on one property and composite index which involve multiple properties from a schema.

Showing Indexes

To retrieve node indexes in the current graph:

SHOW NODE INDEX

To retrieve edge indexes in the current graph:

SHOW EDGE INDEX

The information about indexes is organized into the _nodeIndex or _edgeIndex table. Each table provides essential details about each index:

Creating an Index

You can create an index using the CREATE INDEX statement. Note that each property can only have one single index. The index creation runs as a job, you may run SHOW JOB afterward to verify the success of the creation.

System properties in Ultipa are inherently optimized for query performance and have built-in efficiencies. They do not support indexing.

<create index statement> ::=
  "CREATE INDEX" <index name> "ON" < "NODE" | "EDGE" > <schema name>
  "(" <property index item> [ { "," <property index item> }... ] ")"

<property index item> ::=
  <property name> [ "(" <bytes> ")" ]

Details

• The <index name> must be unique among nodes and among edges, but a node index and an edge index may share the same name.
• For a single index, specifies one <property index item>; for a composite index, lists multiple <property index item>.
• If a specified property is of the type string or text, you must specify the maximum number of <bytes> for the string used in a filter to apply this index. For standard English text, most encodings (like ASCII or UTF-8) use 1 byte per character. For non-English characters, the size may vary. For example, the size of one Chinese character is 3 bytes. Learn more about String Size Limitation.

To create single index named cBalance for the property balance of card nodes:

CREATE INDEX cBalance ON NODE card (balance)

To create single index named name for the property name (string type) of card nodes, restricting the maximum search string length as 10:

CREATE INDEX name ON NODE card (name(10))

To create composite index named transAmountNotes for properties amount and notes (text type, restricting the maximum search string length as 10) for transfer edges:

CREATE INDEX transAmountNotes ON EDGE transfer (amount, notes(10))

Dropping an Index

You can drop an index using the DROP NODE INDEX or DROP EDGE INDEX statement. Dropping an index does not affect the actual property values stored in shards.

A property with an index cannot be dropped until the index is deleted.

To drop the node index cBalance:

DROP NODE INDEX cBalance

To drop the edge index transAmountNotes:

DROP EDGE INDEX transAmountNotes

Using Indexes

Indexes are automatically applied when the corresponding properties are used in query filters, without the need for explicit declarations.

Leftmost Prefix Rule

The order of properties in a composite index matters — queries that match the leftmost properties of the index (i.e., the first property or the first few properties in the defined order) will benefit from the index.

For example:

CREATE INDEX name_age ON NODE user (name(10),age)

• MATCH (u:user WHERE u.name = "Kavi" AND u.age > 20) uses the index.
• MATCH (u:user WHERE u.name = "Kavi") uses the index.
• MATCH (u:user WHERE u.age > 20) doesn't use the index.
• MATCH (u:user WHERE u.name = "Kavi" AND u.age > 20 AND u.grade = 7) uses the index, meanwhile it contains the filtering for the grade property which lacks an index.

String Size Limitation

For indexes with string or text properties, ensure the size of the string used in the filter does not exceed the defined limit.

For example, an index Username is created for the name property of the user nodes with a 8-byte limitation:

CREATE INDEX Username ON NODE user (name(8))

The query below won't utilize the Username index as the specified string Aventurine exceeds the 8-byte limit:

MATCH (n:user {name: "Aventurine"})
RETURN n