Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Select Indexes

  • Capella Operational
  • how-to
    +
    How to select an index for a query.

    Introduction

    Couchbase Capella 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 Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy 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.
    Context

    Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

    Queries

    The following query creates a secondary index on the image_direct_url field in the landmark keyspace.

    CREATE INDEX `idx_image_direct_url`
ON 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 landmark
WHERE image_direct_url IS NOT MISSING;

    For further details and examples, refer to Index Selection.

    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:

    1. Use an index hint within a hint comment, immediately after the SELECT keyword.

    2. 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 data.

    Context

    Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

    Query
    SELECT /*+ INDEX (route def_inventory_route_route_src_dst_day) */ id (1)
FROM route (2)
WHERE sourceairport = "SFO"
LIMIT 1;

    For further details and examples, refer to INDEX.

    Reference and explanation:

    Administrator guides:

    Tutorials:

    Indexes with SDKs: