Create index for 'or' conditions

n1ql
query

#1

I have a query like this one:

SELECT raw visitId FROM bucket WHERE type = "EVENT"
AND (configurableMetadataId="XXX" OR ev.eventTypeId = "XXX)

And I would like to create a GSI but I’m stuck with the OR keyword.
I tried to create 2 separated indexes:

CREATE INDEX idx_et ON bucket(eventTypeId) WHERE type = "EVENT"
CREATE INDEX idx_cm ON bucket(configurableMetadataId) WHERE type = "EVENT"

And I also tried a composite index:
CREATE INDEX idx_et_cm ON bucket(eventTypeId, configurableMetadataId) WHERE type = "EVENT"

But none of these options are working. The composite index would work if it was an AND instead of an OR but then how do I create an index working with an OR condition?

Thank’s.

PS: I’m using Couchbase server 4.5.1.


#2

Try with 4.6.4
In 4.5.1 try with UNION query

SELECT raw visitId FROM bucket WHERE type = "EVENT"
AND configurableMetadataId="XXX"
UNION
SELECT raw visitId FROM bucket WHERE type = "EVENT"
ev.eventTypeId = "XXX";

#3

Try with 4.6.4

What should I try in 4.6.4?
I already accept your answer since the solution you provide me with the UNION keyword is working in 4.5.1, thank’s !


#4

Try original query in 4.6.4

4.6.4
SELECT raw visitId FROM bucket WHERE type = "EVENT"
AND (configurableMetadataId="XXX" OR ev.eventTypeId = "XXX)

4.6.3
SELECT raw visitId FROM bucket WHERE (type = "EVENT"
AND configurableMetadataId="XXX") OR  (type = "EVENT" AND ev.eventTypeId = "XXX);