Hi @matthew.groves, thanks for trying to help.
Let me rephrase the question.
What I’d find out is that the sort order cannot be obtained from the index in Couchbase Server Community Edition. That’s why for sufficiently large number of documents in the database, the request times out, because sorting is performed in memory.
I created following index:
create index type_created_desc on sensorbucket(type,-created) using GSI;
And when I try:
select * from sensorbucket where type = 'data' order by created desc limit 50;
Couchbase uses the index to filter the documents with
type = 'data' but the sorting part of the query is performed in memory.
So, my question is actually threefold:
- Is it true that sort order cannot be obtained from the index in Couchbase Server Community Edition, or I missed something? Documentation does not mention creating indexes in descending order at all.
- If that’s true, is there any other way to get 50 newest results fast? Maybe using some other feature, like views or something? For example, on my laptop it takes ~2 seconds for Couchbase to fetch the results when there is 40k of documents. With MongoDB I get the results for the same number of documents in less than 1 millisecond. I expect similar performance from the Couchbase.
- If that is not possible in Couchbase Server Community Edition at all, is it possible in Couchbase Enterprise Edition? Upgrade to EE might be an option after all, but I need a clear answer that Couchbase CE cannot solve the issue of fetching 50 newest documents fast.
This is general Couchbase Server question, I just want newest 50 results returned fast. If I can use an index and N1QL for that, that’s great, if not I would like to know if there is the other way. That’s why I did not post the question in N1QL forum.