Why my query doesn't use the index?

SQL++
1

I have an index with the following definition

CREATE INDEX `ii` ON `ii_fts`(`ii`) WHERE (not (`ii` = ""))

and then I am trying to query with the query

select raw `ii`  from `ii_fts` where `ii` in ['2303840f-f6e1-4a81-bc09-373cfc95b57e','db43005a-6c04-463f-b098-94e3278c7bf5']

Can’t figure out why is not using the index. The index has data in it, so is not empty

Also tried specifying the index with no difference

USE INDEX (ii USING GSI)
2

Hello @flaviu
SQL++ N1QL has a built in capability to check if a value is null,
I created a sample data set on Couchbase Server 7.2.0 and this index definition seems to work well.
The query executes fully covered by this one index.

CREATE INDEX ii2 ON ii_fts(ii) WHERE ii IS NOT NULL

Give it a try and let us know how it works for you.

Thank you,
Ian McCloy (Couchbase, Director of Product)

3

On the above query that I used, I made it work by adding ii !="" in front of the in

so, basically, the query is now

select raw `ii`  from `ii_fts` where `ii` !="" and  `ii` in ['2303840f-f6e1-4a81-bc09-373cfc95b57e','db43005a-6c04-463f-b098-94e3278c7bf5']

and now seems to use the index

4

Thank you, I will try it. Would be better or faster than the above solution?

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.