Best practice on n1ql index?

SQL++
1

If I have some query, such as:

select **resultRN** from bucket where **conditionCN**;

where:
resultN mention (keyR1,keyR2,…,keyRn)
conditionCN mention (keyC1,keyC2,…,keyCn)
Q1:
how should I create index? create idex using where conditionCN is the best way?
Q2:
should I create index on keys for every condition?
Q3:
what the best practice on balance create index(fast query) and minimize store?

2

If the query always touches the same attributes in the condition and result, you should use:

CREATE INDEX idx ON mybucket(keyC1,keyC2,...,keyCn,keyR1,keyR2,...keyRn);

With Couchbase 4.1, this gives you a covering index. You should verify using EXPLAIN and look for expressions of the form “cover(expr)” in the output.

3

I’ll take up Q2 and Q3.
Q2: it depends on the diversity of your conditions and results. Typically there can be many indexes that can satisfy a subset of your conditions so you can have fewer indexes but acceptable performance for many of your queries.
Q3: again the answer isn’t a crisp decision tree that you can follow. Ultimately, you need to decide which experiences in your app, and queries that drive them, are critical for the experience of your users of your app. Once you identify these queries, you can see what types of gains you can achieve for these queries through considering various indexes. On a large dataset, some queries may not even finish in mins if you don’t have indexes so you may have to create indexes for these queries. In other cases queries may perform perfectly fine even with a full scan of your data because you have a small enough dataset.

One important difference in Couchbase vs other platforms when considering indexing is the cost of indexes. In traditional database systems and many of the NoSQL platforms you will get advice that says “do not create too many indexes because they are costly to your mutation operations (INSERT/UPDATE/DELETE)”. In Couchbase Server, with multi-dimensional scaling of services (data, query and index services), the impact of each index on the core data operations are minimized. We have examples where >100 indexes are created and system sees little impact on core data operations (key based reads or mutations). You can read more about that here: http://developer.couchbase.com/documentation/server/4.1/architecture/services-archi-multi-dimensional-scaling.html

thanks
-cihan

4

Thank you very much. is this means that If I create more indexes, I can add more index services and query services to improve Performance? it is really cool ability.