How can i optimize N1QL Query Performance

SQL++
1

I have currently a Query that gets all documents wher _type = “Residential” and the Images Array = 0 and Record.Dates.CloseDate is less then 1 year from day query is run and the Record.Photos.PhotosCount > 0.

The query is below and works but the time to return it is quite high based on 3 million records…

SELECT META(d).id AS KeyId,
       d.ListingKeyNumeric
FROM rets AS d
WHERE ARRAY_LENGTH(d.Images) = 0
    AND d._type ="Residential"
    AND d.Record.Dates.CloseDate > DATE_ADD_STR(NOW_STR(), -1, "year")
    AND TO_NUMBER(d.Record.Photos.PhotosCount) > 0
ORDER BY d.Record.Dates.ModificationTimestamp DESC

Her is the current Index used…

CREATE INDEX adv_array_length_Images_to_number_Record_Photos_PhotosCount_Reco3293866466 ON rets(array_length(Images),to_number(((Record.Photos).PhotosCount)),((Record.Dates).CloseDate),((Record.Dates).ModificationTimestamp) DESC,ListingKeyNumeric) WHERE (_type = 'Residential')

2

It is optimal.

You can try the following index. May not improve much.

CREATE INDEX ix1 ON rets(Record.Dates.CloseDate,Record.Dates.ModificationTimestamp DESC,ListingKeyNumeric)
WHERE (_type = 'Residential') AND ARRAY_LENGTH(Images) = 0 AND TO_NUMBER(Record.Photos.PhotosCount) > 0;
3

Thanks it shaved of a bit , like .3 sec so i am down from 1.9 to 1.6