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.
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";