How to create effective index for subquery(using LET and IN operators)

SQL++
3

Treat each subquery as separate query and create index based on that. If predicate has dynamic values those will not be pushed to indexer (i.e values must be constants or query parameters).

If you are looking for document that has largest value you can try this

CREATE INDEX ix1 ON mybucket(deviceID);
SELECT maxdoc.*
FROM mybucket AS mb
WHERE mb.deviceID = "10101"
LETTING maxdoc = MAX([mb.recdate, mb])[1];

Also checkout Select non-group by fields using sql query

1 Like