5.2. Indexes for search performance

This section explains how to manage indexes used for search performance.

This section describes how to manage indexes. For query performance purposes, it is important to also understand how the indexes are used by the Cypher planner. Refer to Chapter 6, Query tuning for examples and in-depth discussions on how query plans result from different index and query scenarios. See specifically Section 6.3, “The use of indexes” for examples of how various index scenarios result in different query plans.

For information on index configuration and limitations, refer to Operations Manual → Index configuration.

5.2.1. Introduction

A database index is a redundant copy of some of the data in the database for the purpose of making searches of related data more efficient. This comes at the cost of additional storage space and slower writes, so deciding what to index and what not to index is an important and often non-trivial task.

Once an index has been created, it will be managed and kept up to date by the DBMS. Neo4j will automatically pick up and start using the index once it has been created and brought online.

Cypher enables the creation of indexes on one or more properties for all nodes that have a given label:

  • An index that is created on a single property for any given label is called a single-property index.
  • An index that is created on more than one property for any given label is called a composite index.

Differences in the usage patterns between composite and single-property indexes are described in Section 5.2.3, “Composite index limitations”.

The following is true for indexes:

  • Best practice is to give the index a name when it is created. If the index is not explicitly named, it will get an auto-generated name.
  • The index name must be unique among both indexes and constraints.
  • Index creation is not idempotent. An error will be thrown if you attempt to create the same index twice.

5.2.2. Syntax

Table 5.9. Syntax for managing indexes
Command Description Comment
CREATE INDEX [index_name]
FOR (n:LabelName)
ON (n.propertyName)

Create a single-property index.

Best practice is to give the index a name when it is created. If the index is not explicitly named, it will get an auto-generated name.

The index name must be unique among both indexes and constraints.

Index creation is not idempotent. An error will be thrown if you attempt to create the same index twice.

CREATE INDEX [index_name]
FOR (n:LabelName)
ON (n.propertyName_1,
    n.propertyName_2,
    …
    n.propertyName_n)

Create a composite index.

DROP INDEX index_name

Drop an index

 
CALL db.indexes

List all indexes in the database.

 
DROP INDEX ON :LabelName(propertyName)

Drop a single-property index without specifying a name.

This syntax is deprecated.

DROP INDEX ON :LabelName (n.propertyName_1,
n.propertyName_2,
…
n.propertyName_n)

Drop a composite index without specifying a name.

Section 6.6, “Planner hints and the USING keyword” describes how to make the Cypher planner use specific indexes (especially in cases where the planner would not necessarily have used them).

5.2.3. Composite index limitations

Like single-property indexes, composite indexes support all predicates:

  • equality check: n.prop = value
  • list membership check: n.prop IN list
  • existence check: exists(n.prop)
  • range search: n.prop > value
  • prefix search: STARTS WITH
  • suffix search: ENDS WITH
  • substring search: CONTAINS

However, predicates might be planned as existence check and a filter. For most predicates, this can be avoided by following these restrictions:

  • If there is any equality check and list membership check predicates, they need to be for the first properties defined by the index.
  • There can be up to one range search or prefix search predicate.
  • There can be any number of existence check predicates.
  • Any predicate after a range search, prefix search or existence check predicate has to be an existence check predicate.

However, the suffix search and substring search predicates are always planned as existence check and a filter and any predicates following after will therefore also be planned as such.

For example, an index on :Label(prop1,prop2,prop3,prop4,prop5,prop6) and predicates:

WHERE n.prop1 = 'x' AND n.prop2 = 1 AND n.prop3 > 5 AND n.prop4 < 'e' AND n.prop5 = true AND exists(n.prop6)

will be planned as:

WHERE n.prop1 = 'x' AND n.prop2 = 1 AND n.prop3 > 5 AND exists(n.prop4) AND exists(n.prop5) AND exists(n.prop6)

with filters on n.prop4 < 'e' and n.prop5 = true, since n.prop3 has a range search predicate.

And an index on :Label(prop1,prop2) with predicates:

WHERE n.prop1 ENDS WITH 'x' AND n.prop2 = false

will be planned as:

WHERE exists(n.prop1) AND exists(n.prop2)

with filters on n.prop1 ENDS WITH 'x' and n.prop2 = false, since n.prop1 has a suffix search predicate.

Composite indexes require predicates on all properties indexed. If there are predicates on only a subset of the indexed properties, it will not be possible to use the composite index. To get this kind of fallback behavior, it is necessary to create additional indexes on the relevant sub-set of properties or on single properties.

5.2.4. Examples

5.2.4.1. Create a single-property index

A named index on a single property for all nodes that have a particular label can be created with CREATE INDEX index_name FOR (n:Label) ON (n.property). Note that the index is not immediately available, but will be created in the background.

Query. 

CREATE INDEX index_name FOR (n:Person)
ON (n.surname)

Note that the index name needs to be unique.

Result. 

+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

5.2.4.2. Create a composite index

A named index on multiple properties for all nodes that have a particular label — i.e. a composite index — can be created with CREATE INDEX index_name FOR (n:Label) ON (n.prop1, …​, n.propN). Only nodes labeled with the specified label and which contain all the properties in the index definition will be added to the index. Note that the composite index is not immediately available, but will be created in the background. The following statement will create a named composite index on all nodes labeled with Person and which have both an age and country property:

Query. 

CREATE INDEX index_name FOR (n:Person)
ON (n.age, n.country)

Note that the index name needs to be unique.

Result. 

+-------------------+
| No data returned. |
+-------------------+
Indexes added: 1

5.2.4.3. Drop an index

An index on all nodes that have a label and property/properties combination can be dropped using the name with the DROP INDEX index_name command.

Query. 

DROP INDEX index_name

Result. 

+-------------------+
| No data returned. |
+-------------------+
Indexes removed: 1

5.2.4.4. List indexes

Calling the built-in procedure db.indexes will list all indexes, including their names.

Query. 

CALL db.indexes

Result. 

+------------------------------------------------------------------------------------------------------------------------------------------------+
| id | name             | state    | populationPercent | uniqueness  | type    | entityType | labelsOrTypes | properties    | provider           |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| 2  | "index_58a1c03e" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"     | ["Person"]    | ["location"]  | "native-btree-1.0" |
| 3  | "index_d7c12ba3" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"     | ["Person"]    | ["highScore"] | "native-btree-1.0" |
| 1  | "index_deeafdb2" | "ONLINE" | 100.0             | "NONUNIQUE" | "BTREE" | "NODE"     | ["Person"]    | ["firstname"] | "native-btree-1.0" |
+------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows

5.2.4.5. Deprecated syntax

Drop a single-property index

An index on all nodes that have a label and single property combination can be dropped with DROP INDEX ON :Label(property).

Query. 

DROP INDEX ON :Person(firstname)

Result. 

+-------------------+
| No data returned. |
+-------------------+
Indexes removed: 1

Drop a composite index

A composite index on all nodes that have a label and multiple property combination can be dropped with DROP INDEX ON :Label(prop1, …​, propN). The following statement will drop a composite index on all nodes labeled with Person and which have both an age and country property:

Query. 

DROP INDEX ON :Person(age, country)

Result. 

+-------------------+
| No data returned. |
+-------------------+
Indexes removed: 1