Embedded couchbaselite db query is taking more than two minutes for 5 million db count data

Mobile Couchbase Lite
1 
QueryBuilder.select(
SelectResult.property("type"),
SelectResult.expression(FunctionExpression.count(Expression.all())).as("count"),
SelectResult.expression(FunctionExpression.max(Expression.property("content.updatedAt"))).as(
"updatedAt"
)
)
.from(DataSource.database(db))
.groupBy(Expression.property("type"))
.orderBy(Ordering.property("type"));
const results = await query.execute();```

The above query (with index type_agg on `default`(type, content.updatedAt)) takes two minutes to execute in couchbaselite (on an iPhone device), returning below the result set.

Since couchbaselite query execution is in a sequential manner, the two-minute delay causes the screen freezes in a mobile app.

So, we want a view to count the number of documents in the couchbaselite database which will return the below result set.

Please provide details/suggestions regarding the view count in couchbaselite DB or any alternative solution for above issue.

{
“dbStatistics”: [
{
“type”: “store_item_data”,
“count”: 4567706,
“updatedAt”: “2022-09-02T11:37:03.319Z”
},
{
“type”: “tax_data”,
“count”: 12106
},
{
“type”: “user_data”,
“count”: 8233,
},
{
“type”: “customer_data”,
“count”: 80,
“updatedAt”: “2022-08-30T16:54:32.000Z”
}
]
}

Note - The same query, with the same indexing which we provide in couchabselite DB, executes in the Couchbase DB server in 4 seconds for 5 million DB count data.
2

Have you tried explaining the query? That might provide insight into better indexing.

3

Yes, I have tried that but after indexing also an issue is still there.

4

Can you share the results of you Query.explain?

It is certainly possible that your query just takes a long time. It is, absolutely, possible to construct queries that are sufficiently complex that, especially on a slow file system, they require a long time to complete. If that is the case here, you will probably have to look into one of the common solutions: sharding or something like that.

It is also possible that, after having a look at the results of your Query.explain, we can suggest something that will make the query run faster.

5

Here below is Query.explain result,
Screenshot 2022-09-27 at 4.22.33 PM

6

As you are only grouping by type and ordering by type, the index that is needed for the query is “type”, not “type” + “updatedAt”. You may try that to see if it could help speeding the query. Also as the updatedAt is not in the group-by, the value shown in the query result will be pretty much random, but I’m not sure if that is your intention so I just want to point it out.

Querying all several million records in the database with aggregation and ordering on a mobile device could take sometime. You may think about having some more conditions (WHERE clause) to filtering out some data that are not needed in the query result.

Also, normally when querying data, a good practice is to executing the data on Non-UI thread (e.g. using dispatch queue) to avoid UI freezing.