CODEWITHSUNDEEP
couchbasesql++
Paresh Varde
Paresh Varde

Reputation: 1144

Couchbase Query with ORDER BY DESC and LIMIT is very slow

select t.createdDate, t.createdDateTicks from ic_v10_mammoet t where t.type='asset' and t._sync.rev is not null ORDER BY t.createdDateTicks ASC  LIMIT 10 OFFSET 0

The above query takes 6 seconds to return result and when I remove ORDER BY clause it take only 18 MS

select t.createdDate, t.createdDateTicks from ic_v10_mammoet t where t.type='asset' and t._sync.rev is not null LIMIT 10 OFFSET 0

I have an index on createdDateTicks and it is integer field.

I tried the workaround mentioned at workaround as last comment but that do not work.

Can someone please advise?

Index is:CREATE INDEX asset_createdDateTicks ON ic_v10_mammoet (createdDateTicks) WHERE type = 'asset'

Plan with ORDER BY and Plan without ORDER BY

Upvotes: 0

Views: 1379

Answers (1)

geraldss
geraldss

Reputation: 2445

Here is the workaround.

CREATE INDEX idx_neg_date ON docs( -createDateTicks ) WHERE type = 'asset';

SELECT t.createdDate, t.createdDateTicks
FROM docs AS t
WHERE t.type='asset' AND -t.createdDateTicks IS NOT NULL
ORDER BY -t.createdDateTicks ASC LIMIT 10 OFFSET 0;

Upvotes: 1

Related Questions