USE INDEX clause

The USE INDEX clause specifies the index to use in the query execution.

(Introduced in Couchbase Server 3.0)

Purpose

Use the USE INDEX clause to specify which index to use as part of the query execution. The query engine attempts to use the specified index if the index is applicable for the query.

Prerequisites

For you to select data from a document or keyspace, you must have the query_select privilege on the document or keyspace. For more details about user roles, see Authorization.

Syntax

USE INDEX ( index-ref [ , index-ref2 ]* ) [ USING ( VIEW | GSI ) ]
use index clause

Arguments

index-ref

[Required] String or expression representing the index or indexes to be used for the query.

USING

[Optional; default is USING GSI] Specifies which index form to use.

VIEW

The legacy index form, which lives on the data node.

GSI

The newer and faster Global Secondary Index form, which lives on an index node and can possibly be separate from a data node.

Examples

Example 1a: Use an existing index with GSI in a query.

Create an index of airlines and destination airports, and then use it in a query for flights originating in San Francisco.

CREATE INDEX idx_destinations
ON `travel-sample` (airlineid, airline, destinationairport)
WHERE type="route";

SELECT airlineid, airline, sourceairport, destinationairport
FROM `travel-sample` USE INDEX (idx_destinations USING GSI)
WHERE sourceairport = "SFO";

Example 1b: Use an existing index with VIEW in a query.

The usage of VIEW is identical as that of GSI.

CREATE INDEX idx_destinations
ON `travel-sample` (airlineid, airline, destinationairport)
WHERE type="route";

SELECT airlineid, airline, sourceairport, destinationairport
FROM `travel-sample` USE INDEX (idx_destinations USING VIEW)
WHERE sourceairport = "SFO";