Analytics Index with IN

Analytics
2

Hi Amrish,

You could try splitting the IN clause using UNION ALL as follows:

SELECT DISTINCT RAW DocNum 
FROM ( 
  SELECT DocNum FROM tranData WHERE DocNum = "10112746" AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"
  UNION ALL
  SELECT DocNum FROM tranData WHERE DocNum = "12345" AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"
) t

Alternatively, try dropping your index and create one only on $MdfdTmstmp field
create index tran_ts_ix on tranData($MdfdTmstmp:STRING);
Then your original query should use that index for the BETWEEN predicate:

SELECT DISTINCT RAW DocNum
FROM tranData
WHERE DocNum IN ["10112746", "10112747"]
AND `$MdfdTmstmp` BETWEEN "2022-06-15T00:00:00" AND "2022-06-17T23:59:59"

The LET clause problem requires further investigation on our side.