Selecting Indexes
- how-to
How to select an index for a query.
Introduction
Couchbase Server attempts to select an appropriate secondary index for a query, based on the filters in the WHERE clause. If it cannot select a secondary query, the query service falls back on the the primary index for the keyspace, if one exists.
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:
Specifying Predicates to Select an Index
To specify an index using query predicates, specify the leading query predicates in the WHERE clause in the same order as the index keys in the index.
Use IS NOT MISSING as the predicate for any fields which are required by the index, but which are not actually used for filtering data in the query.
|
For further details and examples, refer to Index Selection.
The following query creates a secondary index on the image_direct_url field in the landmark keyspace.
CREATE INDEX `idx_image_direct_url`
ON `travel-sample`.inventory.landmark(`image_direct_url`);The following query uses a minimal filter on the image_direct_url field to select the idx_image_direct_url index.
SELECT image_direct_url FROM `travel-sample`.inventory.landmark
WHERE image_direct_url IS NOT MISSING;Specifying an Index Hint
You can use an index hint to specify that a query should use a particular index. The index must be applicable to the query.
To specify an index by name:
-
Use the USE clause within the FROM clause.
-
Specify the index name in parentheses.
The following query creates an index of airlines and destination airports.
CREATE INDEX idx_destinations
ON `travel-sample`.inventory.route (airlineid, airline, destinationairport);The following query uses an index hint to select the idx_destinations index.
SELECT airlineid, airline, sourceairport, destinationairport
FROM `travel-sample`.inventory.route USE INDEX (idx_destinations USING GSI)
WHERE sourceairport = "SFO";For further details and examples, refer to USE Clause.