I have created a secondary index for this document type where it ordered by time (e.t).
And I have experienced significant delay or slow query responses while fetching time range is large or towards second half of the array.
From the queries below, query #1
has response time of ~200ms
, while query #2
has response time of 3 seconds
. You can see the increased in response time is probably due to total count of the documents, as it has to filter through from the start.
I want to know if there is a way to speed up this query, or creating a different type of index would help.
PS: I also have an adaptive index, not sure if that will be the cause of the slowness.
Queries:
query #1
SELECT a.e, meta(a).id FROM company a WHERE a.type=‘program’ AND a.rp=‘id_3498587349857’
AND a.l AND a.e.d IS MISSING AND a.e.t >= 0 AND a.e.t < 480000
query #2
SELECT a.e, meta(a).id FROM company a WHERE a.type=‘program’ AND a.rp=‘id_3498587349857’
AND a.l AND a.e.d IS MISSING AND a.e.t >= 36000000 AND a.e.t < 36480000
Document model:
{
"e": {
"t": 117859,
"e": "N",
},
"l": true,
"r": "id_98572945879854",
"rp": "id_3498587349857",
"s": "U",
"d": 1557119400,
"i": 0,
"type": "program"
}
Adaptive Index:
CREATE INDEX `program` ON `company`((distinct (pairs(self)))) WHERE (`type` = "program")
Secondary Index:
CREATE INDEX filter_programsON `company`(type, rp, l, e.d, e.t ASC)