Couchbase lite index with expression

Mobile Couchbase Lite
, , ,
1

Hi,

I’m struggling to understand how indexes work in CBL.
I have the following indexes :

database.createIndex("type", IndexBuilder.valueIndex(ValueIndexItem.property("type")));
database.createIndex("medicalFile",
       IndexBuilder.valueIndex(
            ValueIndexItem.expression(Expression.property("type").equalTo(Expression.string("pulseMedicalFile")))
       ) );

database.createIndex("chat", 
  IndexBuilder.valueIndex(
    ValueIndexItem.expression(Expression.property("type").equalTo(Expression.string("pulseChat")))));

database.createIndex("chatMessages",  
 IndexBuilder.valueIndex(
  ValueIndexItem.expression(Expression.property("type").equalTo(Expression.string("pulseChatMessage")))));

Then, when I use query.explain(), I can see CBL is using only the index named “type”, even when I have something like

Query query = QueryBuilder
                .select(SelectResult.expression(Expression.property("id")),
                        SelectResult.expression(Expression.property("type")),
                        SelectResult.expression(Expression.property("chaId")),
                        SelectResult.expression(Expression.property("usrId")),
                        SelectResult.expression(Expression.property("msgAuthor")),
                        SelectResult.expression(Expression.property("msgType")),
                        SelectResult.expression(Expression.property("msgRecordingDate")),
                        SelectResult.expression(Expression.property("msgValue")),
                        SelectResult.expression(Expression.property("msgDocumentType")),
                        SelectResult.expression(Expression.property("preId")))
                .from(DataSource.database(database))
                .where(
                        Expression.property("type").equalTo(Expression.string("pulseChatMessage"))
                                .and(Expression.property("chaId").equalTo(Expression.string(chaId)))
                                .and(Expression.property("sysActive").equalTo(Expression.booleanValue(true)))
                )
                .orderBy(Ordering.expression(Expression.property("msgRecordingDate")).ascending())
                .limit(Expression.intValue(500));

I don’t understand what index I need to create for this request to be optimized.

Then I tried

database.createIndex("chatMessages2",
                    IndexBuilder.valueIndex(ValueIndexItem.property("type"),
                     ValueIndexItem.expression(Expression.property("type").equalTo(Expression.string("pulseChatMessage")))));

Now, every request using “where type = ‘…’” is using this index.

Can anyone give me some advice ? thanks.

2

Sorry, we haven’t currently supported partial value index (index with non property expression) yet. For now, please only index with the properties that you plan to use in the query which in this case is the ‘type’ property.

Reference Tickets:
https://issues.couchbase.com/browse/CBL-361
https://issues.couchbase.com/browse/CBL-121

3

If you want to query for (pseudocode) type = X and chaId = Y and sysActive = Z, then you should have one index on (type, chaId, sysActive), in any order.

The query optimizer could use 3 separate indexes, one for each property. It would have to look up the matching value in each index, then take the intersection of all the matching documents. I don’t know whether the SQLite optimizer isn’t smart enough to do this, or if it decided that it wouldn’t be cost-effective. (It does use some knowledge of the statistics of the docs in the database.)

4

Thanks for you answers, i understand now why it didn’t work the way I thought!

5

There is a lot of information online on how to use indexes effectively in SQL databases. Most of it is applicable to Couchbase too, since N1QL inherits from SQL (and since Couchbase Lite uses SQLite under the hood.) So you might want to look for some external indexing tutorials or explainers if you want to learn more.

6

Just as a part of self-education, I’d stronlgy recommend you to have a look at this thread https://stackoverflow.com/questions/1108/how-does-database-indexing-work.