CODEWITHSUNDEEP
couchbasesql++
APT
APT

Reputation: 365

N1QL query does not use the expected index

I am trying to create a couchbase N1QL query that will use a specific index. This is my index:

CREATE INDEX index_progressTest ON testbucket(payload.type, payload.display, payload.status, meta(testbucket).id) where payload.type = "Test" AND payload.display = true AND payload.status != "IN_PROGRESS" using GSI;

And this is my query (I use explain to see which index it will use):

EXPLAIN SELECT meta(jmbucket).id FROM testbucket WHERE payload.type = "Test" AND payload.display = true AND payload.status != "IN_PROGRESS";

But it seems that the query will not use my index. If I remove the last part of the "WHERE" clause from both the index and query, then everything works fine.

What should I do to fix it?

Thanks!

Upvotes: 1

Views: 333

Answers (2)

Robert Moore
Robert Moore

Reputation: 1

Remember that Couchbase is Case Sensitive. Make sure that the columns specified in your Create Index script matches the case of the elements in the document. I found this out the hard way. #lessonlearned

Upvotes: 0

geraldss
geraldss

Reputation: 2445

This is fixed in Couchbase 4.5.

If you want to use 4.1, remove this from your index:

AND payload.status != "IN_PROGRESS"

Upvotes: 1

Related Questions