Create index for 'or' conditions

SQL++
,
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);
5

SELECT * FROM bucket WHERE type = ‘instance’ and (field.roleId ==‘102419231’ or queue in [2,1,52]) limit 100;

What should be index for this i tried same as above individual but not improving.

6

Option 1: Uses UnionScan with two indexes

CREATE INDEX ix1 ON bucket(field.roleId) WHERE type = "instance";
 CREATE INDEX ix2 ON bucket(queue) WHERE type = "instance";

OR

     CREATE INDEX ix1 ON bucket(type, field.roleId, queue) WHERE type = "instance";
     If you think you have field.roleId in all documents or want  queue matched want documents that have filed.roleId has value only try the following query.
    SELECT * FROM bucket WHERE type = ‘instance’ and field.roleId  IS  NOT MISSING AND (field.roleId ==‘102419231’ or queue in [2,1,52]) limit 100;