Index when using OR in query

SQL++
1

I have a query like this:

EXPLAIN SELECT *FROM default events
WHERE events.instance=“a” AND events.eventType=“b” and events.resourceType=“event” ORDER BY lastModifiedTime DESC limit 10 offset 0

And the following index is correctly used:

CREATE INDEX event_instance_eventType_index ON default(instance,eventType) WHERE (resourceType = “event”)

but when I add an OR to the query for eventType. The index is not used and the default primary gsi is used instead:

EXPLAIN SELECT *FROM default events
WHERE events.instance=“a” AND events.eventType=“b” OR events.eventType=“c” and events.resourceType=“event” ORDER BY lastModifiedTime DESC limit 10 offset 0

how do I need to configure the index for when using 1 to many OR conditions for an attribute?

2

Hello,
Try to rewrite the ORs conditions with operator IN.
WHERE events.instance="a and events.eventType in (“b”,“c”) and events.resourceType=“event” ORDER BY lastModifiedTime DESC limit 10 offset 0.

Regards
Arturo

1 Like