Hi,
my bucket main
contains potentially millions of user documents like this one:
{
“userId”: …
“customerId”: …
“creationTimestamp”: 1493302910000
"lastUpdateTimestamp": 1593302910000
"type": “User”
}
It shall be possible to specify zero up to two time frames to filter the users:
One filters the users according to creation time,
the other filters users according to their last updated time.
The returned users shall be ordered by creationTimestamp and can be limited to e.g. 1000.
Now I wonder, how should my indices and the N1QL query look like to fetch the correctly filtered users with good performance?
So far, I have tried many different things, but each of them failed in one or the other way, and I am running out of ideas.
The closest I could get to a working solution was creating this index:
CREATE INDEX lastUpdateIndex ON main (type,customerId,creationTimestamp, lastUpdateTimestamp) WHERE type="User";
This N1QL query runs performant (ca. 100ms for 200_000 users) for large timespans, e.g. from 0 to “NOW”.
SELECT userId, customerId, creationTimestamp, lastUpdateTimestamp
FROM `main`
WHERE `type` = "User" AND customerId="testuser"
AND creationTimestamp >= 0 AND creationTimestamp <= 1493380543000
AND lastUpdateTimestamp >= 0 AND lastUpdateTimestamp <= 1493380543000
ORDER BY customerId, creationTimestamp, lastUpdateTimestamp ASC
LIMIT 1000;
As soon as the timespans for the lastUpdateTimestamp get smaller, the performance drops considerably. Especially, if no “creation-timespan” is provided, like e.g. in this query, which takes more than 3s to execute for 200_000 users in the bucket:
SELECT userId, creationTimestamp
FROM `main`
WHERE `type` = "User" AND customerId="testuser"
AND lastUpdateTimestamp >= 0 AND lastUpdateTimestamp <= 1493214005000
ORDER BY customerId, creationTimestamp
LIMIT 1000;