Slow N1Ql Query when trying to get Data based on Array Length

SQL++
1

I am running the following query

SELECT META(d).id AS KeyId,
       d.Record.ListingKeyNumeric
FROM rets AS d
WHERE ARRAY_LENGTH(d.Images) = 0
    AND d._type ="Residential"
    AND d._type IS NOT MISSING
ORDER BY d.Record.Dates.ModificationTimestamp DESC
LIMIT 1000

which i use to get 1000 records a time from my db which has no images in the array. (By images i mean
links to images not actual binary data) When i run this against 1 million records it takes about 10 sec
which seems long.

here is the index used

Index Currently Used

CREATE INDEX adv_array_length_Images_type_Record_ListingKeyNumeric_Record_Dates_ModificationTimestamp ON rets(array_length(Images),(Record.ListingKeyNumeric),((Record.Dates).ModificationTimestamp)) WHERE (_type = ‘Residential’)

image
image2573×288 56.6 KB

As there anything i can do to improve this query ? As close to 10 sec is long on a million and after i load more data not sure how it will be on 25 Million records

2 
CREATE INDEX ix1 ON rets(ARRAY_LENGTH(Images), Record.Dates.ModificationTimestamp DESC, Record.ListingKeyNumeric ) WHERE _type ="Residential";
SELECT META(d).id AS KeyId, d.Record.ListingKeyNumeric
FROM rets AS d
WHERE ARRAY_LENGTH(d.Images) = 0 AND d._type ="Residential"
ORDER BY d.Record.Dates.ModificationTimestamp DESC
LIMIT 1000;

If You are always going to  ARRAY_LENGTH(d.Images) = 0

CREATE INDEX ix1 ON rets(Record.Dates.ModificationTimestamp DESC, Record.ListingKeyNumeric ) WHERE _type ="Residential" AND
      ARRAY_LENGTH(Images) = 0;
SELECT META(d).id AS KeyId, d.Record.ListingKeyNumeric
FROM rets AS d
WHERE ARRAY_LENGTH(d.Images) = 0 AND d._type ="Residential" AND d.Record.Dates.ModificationTimestamp IS NOT NULL
ORDER BY d.Record.Dates.ModificationTimestamp DESC
LIMIT 1000;