Couchbase Index

index is not being applied if I use “emp.EmpSprCd is missing” in below query. If I remove it then index being applied. Could you please suggest to use the index on below query.

Index: (EmpSprCd) for EMPLOYEE

Query
SELECT distinct emp.WaNr AS WaId,emp.WajTypCd,emp.FacKey,waJobCode.WajNa from emp join waJobCode on keys emp.FacKey||’-WORKAREAJOBTYPECODE-’||emp.WajTypCd where emp.Entity=‘EMPLOYEE’ AND (emp.EmpSprCd is missing OR emp.EmpSprCd is NULL OR emp.EmpSprCd <> ‘T’)

Indexer will not index when leading index key is missing. As query is asking MISSING values on leading key the index will not qualify for it.

You can try this.

CREATE INDEX ix1 ON emp(Entity, EmpSprCd);