Creating Indexes
- how-to
How to create primary indexes and secondary indexes.
This guide is for Couchbase Server.
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 SQL++ statement or an SDK call.
-
SQL++
-
.NET
-
Java
-
Node.js
-
Python
To create a primary index, use the CREATE PRIMARY INDEX
command.
-
If required, specify a name for the primary index. If you don’t specify a name, the index is called
#primary
. -
Use the
ON
keyword to specify the keyspace on which to create the index.
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
The following query creates an unnamed primary index on the airline
keyspace.
CREATE PRIMARY INDEX ON airline;
The following query creates a primary index named travel_primary
on the airline
keyspace.
CREATE PRIMARY INDEX travel_primary ON airline;
For further details and examples, refer to CREATE PRIMARY INDEX.
To create a primary index, use the task CreatePrimaryIndexAsync()
on the interface IQueryIndexManager
.
-
Specify the keyspace on which to create the index.
-
If you want to specify a name for the index:
-
Use
CreatePrimaryQueryIndexOptions
to specify the index options. -
In the index options, use the
IndexName
method to specify the index name.
If you don’t specify a name, the index is called
#primary
. -
The following example creates an unnamed primary index.
await cluster.QueryIndexes.CreatePrimaryIndexAsync(
"`travel-sample`",
options => options.IgnoreIfExists(true)
);
The following example creates a named primary index on the specified keyspace.
await cluster.QueryIndexes.CreatePrimaryIndexAsync(
"`travel-sample`",
options => options.IndexName("named_primary_index")
);
Click the View button to see this code in context.
For further details, refer to IQueryIndexManager.
To create a primary index, use the createPrimaryIndex
method.
-
Specify the keyspace on which to create the index.
-
If you want to specify a name for the index:
-
Use
CreatePrimaryQueryIndexOptions
to specify the index options. -
In the index options, use the
IndexName
method to specify the index name.
If you don’t specify a name, the index is called
#primary
. -
The following example creates an unnamed primary index.
CreatePrimaryQueryIndexOptions opts = CreatePrimaryQueryIndexOptions
.createPrimaryQueryIndexOptions()
.ignoreIfExists(true);
cluster.queryIndexes().createPrimaryIndex("travel-sample", opts);
The following example creates a named primary index on the specified keyspace.
CreatePrimaryQueryIndexOptions opts = CreatePrimaryQueryIndexOptions
.createPrimaryQueryIndexOptions()
.indexName("named_primary_index");
cluster.queryIndexes().createPrimaryIndex("travel-sample", opts);
Click the View button to see this code in context.
For further details, refer to QueryIndexManager.
To create a primary index, use the createPrimaryIndex
function on a QueryIndexManager
object.
-
Specify the keyspace on which to create the index.
-
If you want to specify a name for the index:
-
Use
CreatePrimaryQueryIndexOptions
to specify the index options. -
In the index options, use the
name
property to specify the index name.
If you don’t specify a name, the index is called
#primary
. -
The following example creates an unnamed primary index.
await cluster.queryIndexes().createPrimaryIndex(
'travel-sample',
// Don't error if the primary index already exists.
{ ignoreIfExists: true }
)
The following example creates a named primary index on the specified keyspace.
await cluster
.queryIndexes()
.createPrimaryIndex('travel-sample', { name: 'named_primary_index' })
Click the View button to see this code in context.
For further details, refer to QueryIndexManager.
To create a primary index, use the create_primary_index
function on a QueryIndexManager
object.
-
Specify the keyspace on which to create the index.
-
If you want to specify a name for the index:
-
Use
CreatePrimaryQueryIndexOptions
to specify the index options. -
In the index options, use the
index_name
property to specify the index name.
If you don’t specify a name, the index is called
#primary
. -
The following example creates an unnamed primary index.
cluster.query_indexes().create_primary_index(
"travel-sample",
# Don't error if the primary index already exists.
CreatePrimaryQueryIndexOptions(ignore_if_exists=True)
)
The following example creates a named primary index on the specified keyspace.
cluster.query_indexes().create_primary_index(
"travel-sample",
CreatePrimaryQueryIndexOptions(index_name="named_primary_index")
)
Click the View button to see this code in context.
For further details, refer to SQL++ Index Management.
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 SQL++ statement or an SDK call.
-
SQL++
-
.NET
-
Java
-
Node.js
-
Python
To create a secondary index, use the CREATE INDEX
statement.
-
Specify a name for the index.
-
Use the
ON
keyword to specify the keyspace on which to create the index. -
Specify the index key (the expression or expressions to index) in parentheses
()
.
For this example, set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
The following query creates a secondary index on the name
field in the airline
keyspace.
CREATE INDEX `idx-name` ON airline(name);
The following query creates a secondary index on an expression using the name
field in the airline
keyspace.
CREATE INDEX travel_cxname ON airport(LOWER(name));
For further details and examples, refer to CREATE INDEX.
To create a secondary index, use the task CreateIndexAsync()
on the interface IQueryIndexManager
.
-
Specify the keyspace on which to create the index.
-
Specify a name for the index.
-
Specify the field to index.
The following example creates a secondary index on the name
field in the specified keyspace.
await cluster.QueryIndexes.CreateIndexAsync(
"`travel-sample`",
"index_name",
new[] { "name" }
);
Click the View button to see this code in context.
For further details, refer to IQueryIndexManager.
To create a secondary index, use the createIndex
method.
-
Specify the keyspace on which to create the index.
-
Specify a name for the index.
-
Specify the field to index.
The following example creates a secondary index on the name
field in the specified keyspace.
cluster.queryIndexes().createIndex(
"travel-sample",
"index_name",
Arrays.asList("name")
);
Click the View button to see this code in context.
For further details, refer to QueryIndexManager.
To create a secondary index, use the createIndex
function on a QueryIndexManager
object.
-
Specify the keyspace on which to create the index.
-
Specify a name for the index.
-
Specify the field to index.
The following example creates a secondary index on the name
field in the specified keyspace.
await cluster
.queryIndexes()
.createIndex('travel-sample', 'index_name', ['name'])
Click the View button to see this code in context.
For further details, refer to QueryIndexManager.
To create a secondary index, use the create_index
function on a QueryIndexManager
object.
-
Specify the keyspace on which to create the index.
-
Specify a name for the index.
-
Specify the field to index.
The following example creates a secondary index on the name
field in the specified keyspace.
cluster.query_indexes().create_index("travel-sample", "index_name", ["name"])
Click the View button to see this code in context.
For further details, refer to SQL++ Index Management.
Creating a Composite Index
A composite index is a secondary index which contains multiple index keys.
You can create a composite index using a SQL++ statement or an SDK call.
-
SQL++
-
.NET
-
Java
-
Node.js
-
Python
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.
Set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
CREATE INDEX travel_info ON airline(name, id, icao, iata);
For further details and examples, refer to CREATE INDEX.
To create a composite index, specify multiple fields in the index definition.
The following example creates a secondary index on the name
, id
, icao
, and iata
fields in the specified keyspace.
await cluster.QueryIndexes.CreateIndexAsync(
"`travel-sample`",
"index_travel_info",
new[] { "name", "id", "icao", "iata" }
);
Click the View button to see this code in context.
For further details, refer to IQueryIndexManager.
To create a composite index, specify multiple fields in the index definition.
The following example creates a secondary index on the name
, id
, icao
, and iata
fields in the specified keyspace.
cluster.queryIndexes().createIndex(
"travel-sample",
"index_travel_info",
Arrays.asList("name", "id", "icao", "iata")
);
Click the View button to see this code in context.
For further details, refer to QueryIndexManager.
To create a composite index, specify multiple fields in the index definition.
The following example creates a secondary index on the name
, id
, icao
, and iata
fields in the specified keyspace.
await cluster
.queryIndexes()
.createIndex('travel-sample', 'index_travel_info', [
'name',
'id',
'icao',
'iata',
])
Click the View button to see this code in context.
For further details, refer to QueryIndexManager.
To create a composite index, specify multiple fields in the index definition.
The following example creates a secondary index on the name
, id
, icao
, and iata
fields in the specified keyspace.
cluster.query_indexes().create_index(
"travel-sample",
"index_travel_info",
["name", "id", "icao", "iata"]
)
Click the View button to see this code in context.
For further details, refer to SQL++ Index Management.
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.
Set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
CREATE INDEX idx_hotel_id ON 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:
-
Use the
ALL
keyword to index all values in the specified fields, orDISTINCT
to index only distinct values. -
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.
Set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
CREATE INDEX travel_sched ON 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
.
Set the query context to the inventory
scope in the travel sample dataset.
For more information, see Setting the Query Context.
CREATE INDEX travel_eat ON 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.