CREATE INDEX

CREATE INDEX

The CREATE INDEX statement allows you to create a secondary index. Secondary indexes contain a filtered or a full set of keys in a given bucket. Secondary indexes are optional but increase query efficiency on a bucket.

CREATE INDEX is by default a synchronous operation. CREATE INDEX statement blocks until the operation finishes. Index building starts by creating a task that is queued for index build. After this phase, if you lose connectivity, index build operation continues to happen in the background. You can also run index creation asynchronously using the "defer_build" clause. In the async mode, CREATE INDEX starts a task to create the primary index and returns as soon as the task is queued for execution. The full index creation operation happens in the background.

Both GSI and View indexes provide a status field and mark index status pending. With GSI indexer, index status continues to report "pending". This status field and other index metadata can be queried using system:indexes.

RBAC Privileges

User executing the CREATE INDEX statement must have the Query Manage Index privilege granted on the keyspace/bucket. For more details about user roles, see Authorization.

Important: Indexes cannot be built concurrently on a given bucket unless the defer_build option of the CREATE INDEX statement is used in combination with BUILD INDEX statement. The following error is reported if a second index creation operation is kicked off before the completion of the ongoing index creation.
"errors": [{"code": 12014, 
    "msg": "error: Build Already In Progress. Bucket BUCKET_NAME. Index INDEX_NAME. Index state: pending"}]

You can create multiple identical secondary indexes on a bucket for better index availability and place them on separate nodes using the "nodes" clause.

Note: Couchbase Server 4.5 release adds the capability to create global indexes on array elements and optimizes the execution of queries involving array elements. See Array Indexing for details.

create-index:

CREATE INDEX [index_name] ON named_keyspace_ref( expression [ , expression ] * )
    WHERE filter_expressions
    [ USING GSI | VIEW ]
    [ WITH { "nodes": [ "node_name" ], "defer_build":true|false , "num_replica": num_replica } ];

index_name:

A unique name that identifies the index. This parameter is required.

Valid GSI index names can contain any of the following characters: A-Z a-z 0-9 # _, and must start with a letter, [A-Z a-z]. The minimum length of an index name is 1 character and there is no maximum length set for an index name. When querying, if the index name contains a '#' or '_' character, you must enclose the index name within backticks.

named-keyspace-ref( expression [ ,expression ]* )

[ namespace-name :] keyspace-name 

keyspace-name:

An identifier that refers to the bucket name. Specifies the bucket as the source for which the index needs to be created. You can add an optional namespace name to the keyspace name in this way:

namespace-name : keyspace-name

For example, main:customer indicates the customer keyspace in the main namespace. If the namespace name is omitted, the default namespace in the current session is used.

Expression index-key

Refers to an attribute name or a scalar function or an ARRAY expression on the attribute. This constitutes an index-key for the index. See Array Indexing for more details about the array expressions.

USING GSI | VIEW

USING clause specifies the index type to use. Secondary indexes can be created using global secondary indexes (GSI) or views. If the USING clause is not specified, by default GSI is used as the indexer.

WITH options

With clause is used to specify additional options with the GSI type indexes.

"nodes":["node name"]

A single global secondary index can be placed on specific nodes that run the indexing service. The "nodes" option allows you to specify the nodes that the index is placed on. If nodes is not specified, nodes running the index service are randomly selected to host the index, based on the number of replicas. Multiple nodes can be specified to distribute replicas of an index amongst multiple nodes, for example:
CREATE INDEX productName_index1 ON bucket_name(productName, ProductID) 
       WHERE type="product" USING GSI 
       WITH {"nodes":["node1:8091", "node2:8091", "node3:8091"]};

If specifying both nodes and num_replica, the number of nodes in the array must be one greater than the specified number of replicas otherwise the index creation will fail.

Important: The node names passed to the nodes parameter must include the cluster administration port (by default 8091). For example WITH {"nodes": ["192.0.2.0:8091"]} instead of WITH {"nodes": ["192.0.2.0"]}.

"defer_build":true | false

With defer_build set to true, CREATE INDEX operation queues the task for building the index but immediately pauses the building of the index of type GSI. Index building require an expensive scan operation. Deferring building of the index with multiple indexes can optimize the expensive scan operation. Admins can defer building multiple indexes and, using BUILD INDEX statement, multiple indexes to be built efficiently with one efficient scan of bucket data.

With defer_build set to false, CREATE INDEX operation queues the task for building the index and immediately kicks off the building of the index of type GSI.

"num_replica": num_replica

num_replica specifies the number of replicas of the index to create. The indexer will automatically distribute these indexes amongst index nodes in the cluster for load balancing and high availability purposes. When creating an index with replicas in this manner, the indexer will attempt to distribute the replicas based on the server groups in use in the cluster where possible. If the value passed to num_replica is not less than the number of index nodes in the cluster then the index creation will fail.

Attention: We recommend that you do not create (or drop) secondary indexes when any node with a secondary index role is down as this may result in duplicate index names.

Using the meta().id function

You can use the meta().id function when creating an index. The meta().id function does not require a parameter; it implicitly uses the keyspace being indexed.

CREATE INDEX id_ix on `beer-sample`(meta().id);
The meta().id function in a query is given an expression; if it resolves to a keyspace alias, the requested field (id) is returned.
SELECT b.name, meta(b).id 
FROM `beer-sample` b 
WHERE meta(b).id > "g" limit 1;

The only supported meta() field in the CREATE INDEX statement for Couchbase Server version 4.5 is meta().id.

Using indices for aggregates

If there is an index on the expression of an aggregate, that index may be used to satisfy the query. For example, given the index " abv_idx" created using the following statement:
CREATE INDEX abv_idx ON `beer-sample`(abv);
The query engine will use the index " abv_idx" for the following query:
SELECT min(abv), max(abv) FROM `beer-sample`;

Examples

The following example creates a secondary index that contains beers with an abv value greater than 5 on the node 192.0.2.1:

CREATE INDEX over5 ON `beer-sample`(abv) WHERE abv > 5 USING GSI WITH {"nodes": ["192.0.2.1:8091"]};

The following example creates a secondary index on the beer-sample bucket and then queries system:indexes for status of the index:

CREATE INDEX `beer-sample-type-index` ON `beer-sample`(type) USING GSI;
SELECT * FROM system:indexes WHERE name="beer-sample-type-index";

The following example creates the same secondary index by using the deferred build option and then queries system:indexes for status of the index:

CREATE INDEX `beer-sample-type-index` ON `beer-sample`(type) USING GSI
    WITH {"defer_build":true};
SELECT * FROM system:indexes WHERE name="beer-sample-type-index";

Because the deferred build option was enabled, the output from the query on system:indexes shows beer-sample-type-index shows the index has not finished building ("state": "pending").

The following example uses the BUILD INDEX statement to kick off the deferred build on the beer-sample-type-index index and then queries system:indexes for status of the index:

BUILD INDEX ON `beer-sample`(`beer-sample-type-index`) USING GSI;
SELECT * FROM system:indexes WHERE name="beer-sample-type-index";

This time the query on system:indexes shows that the index is built ("state": "online").

Limitations

  • The total size of the index keys cannot exceed 4K for a single document. Index key size is calculated using the total size of all the expressions being indexed in a single document. If an index keys size exceeds 4K, it will be skipped. The following error is logged to indicate that an item is skipped when building the index: "Encoded secondary key is too long" in the indexer.log file. The indexer.log file is included in cbcollect_info output.