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

SQL++
1

example query

SELECT q1, q2 LET q1 = (Select ht.* FROMmybucketas mb WHERE mb.recdate IN (SELECT RAW MAX(recdate) FROMmybucketas mbt WHERE mbt.deviceID = "10101")), q2 = (Select ht.* FROMmybucketas mb WHERE mb.recdate IN (SELECT RAW MAX(recdate) FROMmybucketas mbt WHERE mbt.deviceID = "10001"));

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