CODEWITHSUNDEEP
databasecouchbase
Prashant Pandey
Prashant Pandey

Reputation: 4662

Couchbase Cannot See Secondary Index

I created an index on my default bucket:

CREATE INDEX queryIndex on default (customerId, orderState, locationId, timestamps.lastUpdatedDateTime) USING GSI;

Here is my query:

SELECT orderId FROM default WHERE orderState="Open" and DATE_DIFF_STR(CLOCK_LOCAL(),MILLIS_TO_STR(timestamps.lastUpdatedDateTime),'day') > 10;

For some reason, Couchbase cannot find the index. It asks me to create one. Can someone tell me what am I doing wrong?

Upvotes: 1

Views: 26

Answers (1)

Johan Larson
Johan Larson

Reputation: 1890

The system is not able to use that index for that query because the fields in the WHERE clause are not leading fields in the index. Also, if you want to use this index as a covering index (meaning additional fetches of the full document are not needed), all the fields you mention in the query need to be included in the index.

The orderState="open" predicate looks like the place to start. Move orderState to the beginning of the fields listed in the index.

Upvotes: 2

Related Questions