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 an index hint within a hint comment, immediately after the SELECT keyword.
-
In the index hint, specify the keyspace to which the hint applies, and the index to use.
This example uses an index hint to select the index def_inventory_route_route_src_dst_day, which is installed with the travel-sample bucket.
SELECT /*+ INDEX (route def_inventory_route_route_src_dst_day) */ id (1)
FROM `travel-sample`.inventory.route (2)
WHERE sourceairport = "SFO"
LIMIT 1;For further details and examples, refer to INDEX.