CODEWITHSUNDEEP
couchbasesql++
Alpcan Yıldız
Alpcan Yıldız

Reputation: 741

Count query works too slow in Couchbase

I am using couchbase:community-6.0.0 in my Spring application. I have like 250.000 records in database. My database query works very fast without using COUNT query command.

SELECT app.*, META(app).id AS id FROM app WHERE ( deleted = FALSE OR 
deleted IS MISSING ) AND _class = 
“com.myexample.app.device.data.model.DeviceEntity” AND appId = 
“something” AND dp.language = “somelanguage” LIMIT 100 OFFSET 0

This query works very well and fast…Response time smaller than 50ms.

However

SELECT COUNT(*) AS count FROM app WHERE ( deleted = FALSE OR deleted 
IS MISSING ) AND _class = 
“com.myexample.app.device.data.model.DeviceEntity” AND appId = 
“something”

It takes 1 minute. I can not reduce.

Indexes

primary

CREATE INDEX class_appId_idx ON app(_class,appId)

CREATE INDEX ix1 ON app(_class,appId,ifmissing(deleted, false))

What is the solution of this ? I think index does not work with a count ? Any advice please, how can I achieve this?

Note : I tried with EE edition, did not work.

Upvotes: 2

Views: 689

Answers (2)

Alpcan Yıldız
Alpcan Yıldız

Reputation: 741

It works in miliseconds with using enterprise-6.0.0

Upvotes: 0

Johan Larson
Johan Larson

Reputation: 1890

The system isn't able to match the index to the query. Sometimes the optimizer isn't all that bright. Try this:

create index ix_test on test(_class, appId) WHERE deleted = FALSE OR deleted IS MISSING

That will use the index.

Generally speaking, because of how we build the indexes, we have trouble with IS MISSING clauses. But putting that bit in the WHERE clause of the index makes it work. But this is a very specialized index. Consider changing your data so the "deleted" field is always present.

Upvotes: 2

Related Questions