CODEWITHSUNDEEP
couchbasesql++
Jahanzaib Aslam
Jahanzaib Aslam

Reputation: 2834

Couchbase 4 “ORDER BY” performance in Stable Version

DUPLICATE: Couchbase 4 beta “ORDER BY” performance

Like Question title shows, I am facing huge response delay like 13s for one call using Couchbase 4 (N1QL) ORDER BY clause. If I don't use ORDER BY clause every thing is fine.

My Primary Index is 

Definition: CREATE PRIMARY INDEX `#primary` ON `default` USING GSI

and secondary index is

Definition: CREATE INDEX `index_location_name` ON `default`(`name`) USING GSI

N1QL Query

req.params.filter can be any key in the location document.

SELECT _id AS id FROM default WHERE type = 'location' ORDER BY " + req.params.filter + (req.query.descending?' DESC':'') + " LIMIT " + limit + " OFFSET " + skip

Location Document in my Bucket is 

{
  "_id": "location::370794",
  "name": "Kenai Riverside Fishing",
  "avgRating": 0,
  "city": "Cooper Landing",
  "state": "Alaska",
  "country": "USA",
  "zipCode": "99572",
  "created": "2013-07-10T17:30:00.000Z",
  "lastModified": "2015-02-13T12:34:36.923Z",
  "type": "location",
}

Any one can tell why ORDER BY clause is making so much delay?

Upvotes: 2

Views: 698

Answers (1)

gafi
gafi

Reputation: 12729

I believe couchbase is not built to handle queries that can be ordered by any field. Since, ordering is an expensive operation in CB, it's always recommended to create an index based on the sorting fields. Also, if the index is built in ascending order, then it can't be used for descending ordering and vice versa. Your best option with CB is to create all the possible indices with asc & desc order if feasible.

I'd also recommend that you consider if elasticsearch would be a better fit for your dynamic search use cases.

Upvotes: 0

Related Questions