Create Indexes

Capella Operational

how-to

How to create primary indexes and secondary indexes.

Introduction

You don’t need to create an index to query a keyspace. However, an index can help you to query a keyspace more efficiently. 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 Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy 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. They may improve the efficiency of 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.

Context

For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Queries

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:
1. Use CreatePrimaryQueryIndexOptions to specify the index options.
2. 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:
1. Use CreatePrimaryQueryIndexOptions to specify the index options.
2. 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:
1. Use CreatePrimaryQueryIndexOptions to specify the index options.
2. 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:
1. Use CreatePrimaryQueryIndexOptions to specify the index options.
2. 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 ().

Context

For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Queries

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.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

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.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

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, or DISTINCT 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.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

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.

Context

Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

Query

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.

Reference and explanation:

Using Indexes

Administrator guides:

Indexes with SDKs:

C | .NET | Go | Java | Node.js | PHP | Python | Ruby | Scala

Create Indexes

Introduction

Creating a Primary Index

Creating a Secondary Index

Creating a Composite Index

Creating an Index on Metadata

Creating an Index on an Array

Creating a Partial Index

Creating a Covering Index

Related Links