CODEWITHSUNDEEP
couchbasesql++
Bifrost
Bifrost

Reputation: 427

Couchbase N1ql queries

I have two question regarding N1QL query in Couchbase.

1: Let suppose I have user table where userid is document key and then i fire a query like this

select * from mybucket use keys["1234"];

2: Let suppose userid is not a document key and then i create a secondary index on userid

select * from mybucket where userid=1234;

So my question is, which query would perform faster ?

Second question is,

Let suppose I have user table where userid is document key

select * from mybucket where meta().id="1234";

This query does not run and give me "No index available on keyspace". It is a document key, it should run like "use keys". I tried to create a secondary index on userid but it says index can not be created since this field is not the part of document(obviously, it is a document key)

Upvotes: 1

Views: 471

Answers (1)

Johan Larson
Johan Larson

Reputation: 1890

The first query will run fastest. Naming the specific key directly in a USE KEYS clause lets Couchbase retrieve the record directly in a single request. The second approach, using an index, will be slightly slower, because the system will first have to make a request to the index to get the document id, and then retrieve the record itself. The second approach will still be very very fast, but not quite as fast as the first one.

Yeah, depending on what version you are using, we may not be fully optimizing that third case. Use USE KEYS if you can.

Upvotes: 4

Related Questions