CB Indexes with some common properties

Mobile Couchbase Lite
1

Hi,

I would like to know the best approach for making the CB indexes in the following case.

Suppose I have 2 different indexes

let index = IndexBuilder.valueIndex(items:
    ValueIndexItem.expression(Expression.property("type")),
    ValueIndexItem.expression(Expression.property("backendState")),
    ValueIndexItem.expression(Expression.property("number")),
    ValueIndexItem.expression(Expression.property("isCompleted")),
    ValueIndexItem.expression(Expression.property("systemStatus")),
    ValueIndexItem.expression(Expression.property("name")))
try database.createIndex(index, withName: "FirstIndex")

And

let index = IndexBuilder.valueIndex(items:
    ValueIndexItem.expression(Expression.property("type")),
    ValueIndexItem.expression(Expression.property("backendState")),
    ValueIndexItem.expression(Expression.property("systemStatus")),
    ValueIndexItem.expression(Expression.property("editedBy")),
    ValueIndexItem.expression(Expression.property("cancelledBy")),
    ValueIndexItem.expression(Expression.property("completedBy")),
    ValueIndexItem.expression(Expression.property("reason")))
try database.createIndex(index, withName: "SecondIndex")
  1. Here the two indexes are sharing common properties. The properties type, backendState and systemStatus are common.
  2. Some of the properties in this index won’t present always based on the type of the document. The properties completedBy and cancelledBy are mutually exclusive.

What is the best way to create the indexes in such scenarios?

2

You simply create both indexes. There will be duplicate data in them, but that’s just the way indexes work.

3

Thanks @jens.
I tried to add those two indexes soon I created the database and this time data may or maynot be empty, but later it will get updated . When I query from database, my query.explain() doesn’t show my index name. I went through one of the tutorial, but other than creating index, I didn’t see nothing specific we are doing with index. I believe when we query it should be internally handled. Anything else I’m missing other than creating these indexes?

Thanks in advance!

4

It depends on your query. Query optimization is complex; have you worked with indexing before in SQL databases or in server N1QL? If the explain method doesn’t indicate the index is being used, that means the index isn’t appropriate for that query.

5

Got it thanks. Actually, I don’t have any experience with SQL/N1QL DB. But your observation can direct me to make the indexing better.

6

The key thing to know is that an index on A, B, C, D can help with a set of comparisons of those properties, joined by AND. But!

  • There can’t be gaps, so it can help with tests on A, B, C but not on A, B, D. (In the latter case it can only help with looking up A and B.)
  • These all have to be equality tests except for the rightmost one. So it can handle A=a and B=b and C<c, but not A=a and B>b and C=c. (In the latter case it can only help find A and B, not C.)

The optimizer can decide not to use an index for other reasons. It has heuristics based on the number of documents in the database vs the number of rows in the index, for example.

1 Like
7

Also SQLite has a good documentation about database index. The concept could be applied to CBL Value Index as well.

https://www.sqlite.org/queryplanner.html

1 Like