A newer version of this documentation is available.

View Latest
February 16, 2025
+ 12
How to create primary indexes and secondary indexes.

Introduction

You must create an index on a keyspace to be able to query that keyspace. The Index service enables you to create two types of index: primary indexes and secondary indexes.

If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:

Creating a Primary Index

A primary index is an index of document keys. Primary indexes are optional, and are only required for running ad-hoc queries that are not supported by a secondary index.

You can create a primary index using a N1QL statement or an SDK call.

Prior to the SDK API 3.3 releases (released alongside Server 7.1), the SDK calls only enabled you to create indexes in the default collection and default scope within a bucket. A N1QL statement enables you to create indexes in any collection and scope within a bucket.

To create a primary index, use the CREATE PRIMARY INDEX command.

  1. If required, specify a name for the primary index. If you don’t specify a name, the index is called #primary.

  2. Use the ON keyword to specify the keyspace on which to create the index.


The following example creates an unnamed primary index on the airline keyspace.

CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline;

The following example creates a primary index named travel_primary on the airline keyspace.

CREATE PRIMARY INDEX travel_primary ON `travel-sample`.inventory.airline;

For further details and examples, refer to CREATE PRIMARY INDEX.

Creating a Secondary Index

A secondary index is actually the main type of index that queries use. For this reason, they are also known as Global Secondary Indexes or GSIs. You can create a secondary index on any fields or expressions necessary to support your queries.

You can create a secondary index using a N1QL statement or an SDK call.

Prior to the SDK API 3.3 releases (released alongside Server 7.1), the SDK calls only enabled you to create indexes in the default collection and default scope within a bucket. A N1QL statement enables you to create indexes in any collection and scope within a bucket.

To create a secondary index, use the CREATE INDEX statement.

  1. Specify a name for the index.

  2. Use the ON keyword to specify the keyspace on which to create the index.

  3. Specify the index key (the expression or expressions to index) in parentheses ().


The following example creates a secondary index on the name field in the airline keyspace.

CREATE INDEX travel_name ON `travel-sample`.inventory.airline(name);

The following example creates a secondary index on an expression using the name field in the airline keyspace.

CREATE INDEX travel_cxname ON `travel-sample`.inventory.airport(LOWER(name));

For further details and examples, refer to CREATE INDEX.

Creating a Composite Index

A composite index is a secondary index which contains multiple index keys.

You can create a composite index using a N1QL statement or an SDK call.

Prior to the SDK API 3.3 releases (released alongside Server 7.1), the SDK calls only enabled you to create indexes in the default collection and default scope within a bucket. A N1QL statement enables you to create indexes in any collection and scope within a bucket.

To create a composite index, specify multiple index keys in the index definition, separated by commas.


The following example creates a secondary index on the name, id, icao, and iata fields in the airline keyspace.

CREATE INDEX travel_info ON `travel-sample`.inventory.airline(name, id, icao, iata);

For further details and examples, refer to CREATE INDEX.

Creating an Index on Metadata

You can also create a secondary index using document metadata.

To index metadata information, use the META() function in the index key.

The following example creates a secondary index on the document key.

CREATE INDEX idx_hotel_id ON `travel-sample`.inventory.hotel (META().id);

For further details and examples, refer to Indexing Metadata Information.

Creating an Index on an Array

You can use an array index to optimize queries on fields which are nested within array elements.

To create an array index, specify the index key as follows:

  1. Use the ALL keyword to index all values in the specified fields, or DISTINCT to index only distinct values.

  2. Use a field name to index the entire array, or use an ARRAY operator to index nested fields within the array.

The following example creates an index on distinct values of the day field within the schedule field.

CREATE INDEX travel_sched ON `travel-sample`.inventory.route
(DISTINCT ARRAY v.day FOR v IN schedule END);

For further details and examples, refer to Array Indexing.

Creating a Partial Index

A partial index is an index on a subset of documents within a keyspace — for example, just the documents which have a specific schema.

To create an index on a subset of documents, use the WHERE clause to specify the distinguishing field(s) for that subset.

The following example creates an index on documents in which the value of the activity field is eat.

CREATE INDEX travel_eat ON `travel-sample`.inventory.landmark(name, id, address)
WHERE activity='eat';

For further details and examples, refer to Partial Index.

Creating a Covering Index

A covering index is an index which contains all the fields in the query projection, not just the fields that are required for joins or filtering. A covering index is therefore usually a composite index. If a query uses a covering index, the query can get all the data it needs from the index, and the Query service does not have to make a fetch request to the Data service.

To create a covering index, make sure the index includes all the fields and expressions required by the query.

For further details and examples, refer to Covering Indexes.

Reference and explanation:

Administrator guides:

Indexes with SDKs: