Index for a query with OR statement

Couchbase Server
,
1

Hi,
I have a query with an OR statement that looks like this:

SELECT * FROM BucketName WHERE Obj.Name='somwthing' OR Obj.NameEng='something'

I have created these indexes, but none of them is being used (I am using the “explain” query to check).

CREATE INDEX `ind1` ON `BucketName`(Obj.NameEng)
CREATE INDEX `ind2` ON `BucketName`(Obj.Name)
CREATE INDEX `ind3` ON `BucketName`(Obj.Name,Obj.NameEng)
CREATE INDEX `ind4` ON `BucketName`(Obj.Name or Obj.NameEng)

What is the correct create index statement for the above query?
Currently using Couchbase 4.5 community , with a plan to move to 6.0 very soon.
Thanks!

2

Couchbase Indexer doesn’t index document the document when leading index key is MISSING in the document. Due to that every OR part of predicate must have leading index key.
In 4.5 You have the following option.

SELECT * FROM BucketName WHERE Obj.Name='somwthing'
UNION
SELECT * FROM BucketName WHERE Obj.NameEng='somwthing';

In 5+ Abobe query uses UnionScan and uses both the indexes.

If you are looking same value for both the names you can try this.

CREATE INDEX `ix1` ON `BucketName`( DISTINCT ARRAY name FOR name IN [Obj.Name, Obj.NameEng] END);
SELECT * FROM BucketName WHERE ANY name IN [Obj.Name, Obj.NameEng]  SATISFIES name = "something" END;