Team, urgent we are in production and w ehave one issue on index.
SELECT * FROM optima WHERE type = ‘processinstance’ and (assignee.roleId==‘44a906ad-0889-44b7-b890-588f27632b61’ or processQueueId in [4009]) limit 10
created 2 index for above query
CREATE INDEX idx_type_assigneerole ON optima(type,(assignee.roleId)) WHERE (type = “processinstance”) WITH { “nodes”:[ “10.131.108.13:8091” ] }
CREATE INDEX idx_type_processQueueId ON optima(type,processQueueId) WHERE (type = “processinstance”) WITH { “defer_build”:true }
Above both the index scanned when i’m not using LIMIT on query and its fast response, the same when i use LIMIT it hangs can you please guide how to create Index for following QUERY
SELECT * FROM optima WHERE type = ‘processinstance’ and (assignee.roleId==‘44a906ad-0889-44b7-b890-588f27632b61’ or processQueueId in [4009] )and ANY var IN processInstHistory SATISFIES var.createDate >='1970-01-01T00:00:00.000Z’END LIMIT 100 OFFSET 0
I am not sure why it hangs. You can try latest CB version.
CREATE INDEX ix1 ON optima ( assignee.roleId) WHERE type = "processinstance";
CREATE INDEX ix2 ON optima ( processQueueId) WHERE type = "processinstance";
SELECT o.*
FROM optima AS o
WHERE o.type = "processinstance"
AND (o.assignee.roleId = "44a906ad-0889-44b7-b890-588f27632b61" OR o.processQueueId IN [4009] )
LIMIT 10;
SELECT o.*
FROM optima AS o
WHERE o.type = "processinstance"
AND o.assignee.roleId = "44a906ad-0889-44b7-b890-588f27632b61"
UNION
SELECT o.*
FROM optima AS o
WHERE o.type = "processinstance"
AND o.processQueueId IN [4009]
LIMIT 10;
You can also try this if LIMIT is high
SELECT *
FROM optima USE KEYS ( SELECT RAW META(o).id
FROM optima AS o
WHERE o.type = "processinstance"
AND o.assignee.roleId = "44a906ad-0889-44b7-b890-588f27632b61"
UNION
SELECT RAW META(o).id
FROM optima AS o
WHERE o.type = "processinstance"
AND o.processQueueId IN [4009]
LIMIT 10)];
For other query use following indexes and modify query as above
CREATE INDEX ix3 ON optima ( assignee.roleId, DISTINCT ARRAY c.createDate FOR c IN processInstHistory END) WHERE type = "processinstance";
CREATE INDEX ix3 ON optima ( processQueueId, DISTINCT ARRAY c.createDate FOR c IN processInstHistory END) WHERE type = "processinstance";