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.
I think @geraldss might have a better idea from the N1QL point of view, so I’m tagging him. Based on this ticket on couchbase.org, I think you might be seeing an issue that was fixed in Couchbase 4.5 (which is currently available as an Enterprise release only, but will eventually be a Community release). Feel free to try out Enterprise Edition on your laptop, you’ll only need a license when you go into production.
Another option besides N1QL is a map/reduce view. Maybe. This is an older feature of Couchbase Server and many of the use cases are replaced by N1QL, but it is still an option, and is probably better for high-write applications. Sorting is going to make that more complex to write, however.
You can create partition negative functional index on timestamp.
CREATE INDEX ix1 on sensorbucket(-timestamp) WHERE type = 'data';
In 4.5 and above you can use the following query, Query ORDER BY matches with index keys and will use Index Order.
SELECT * FROM sensorbucket WHERE type = 'data' and -timestamp IS NOT MISSING ORDER BY -timestamp LIMIT 50;
In 4.1.1 Just skip the query order by clause that way the data returned will follow the index order (query needs to use the index ix1, You can verify the results).
SELECT * FROM sensorbucket USE INDEX(ix1) WHERE type = 'data' and -timestamp IS NOT MISSING LIMIT 50;
SELECT s.* FROM (SELECT RAW META().id FROM sensorbucket USE INDEX(ix1) WHERE type = 'data' and -timestamp IS NOT MISSING LIMIT 50) qa INNER JOIN sensorbucket s ON KEYS qa ORDER BY s.timesatmp DESC LIMIT 50;
It seems that it works now. On 4.1.1 CE I created the index: create index ix1 on sensorbucket(owner,type,-created) using GSI;
and the query: select * from sensorbucket where owner = 'alice' and type = 'data' limit 10;
returns what seems to be correctly sorted results in ~30ms. Which is not as fast as other databases, but a lot faster than before. It’s usable now.
I also tried out the solution for 4.5 version and it works fine as well.
This is very basic requirement and I wonder why relevant information is not available in the official documentation. I had to spend a lot of time trying to find out if it is possible at all to created indexes on fields in descending order and what the syntax is. Also, in this tweet from the official N1QL account it’s stated that index order can be obtained from the index in version 4.5 and above.
Did I miss some part of the docs or the info about index usage (descending, order by, etc.) is not available officially (in the documentation)?