I am trying to execute the below query which returns 9 records from around 5 lakhs documents and it is taking around 7 seconds to execute.
SELECT _id,name,url,tags,status,createdAt FROM content where type = “IMAGE” and _type = “UserAsset”
AND createdAt IS NOT MISSING
AND ANY v IN [LOWER(name),LOWER(tags)] SATISFIES v LIKE “%a9e82812%” END
for this query i am using indexing as below
CREATE INDEX ix1c_UserAsset ON content(createdAt DESC,type,_type,status, name,DISTINCT tags,url,_id,categoryName) WHERE _type=“UserAsset”
The query should return result in less than a second but it is taking 7 seconds to execute.Am I missing something?