main contains potentially millions of user documents like this one:
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;