Index for Order By in n1ql

user_7639681 0
Viewed 281

Hope everyone is doing well.

I was trying to optimize a N1Ql query with order by clause by creating index.I got a doubt does order of orderby columns and where column should be kept same to have a proper index or ordering doesn't matter in couchbase 6.0.3. Below is query

Select * from employee where type= employee and number='123' and division='456'
order by class,number

Index could be

Create index idx_123 on employee(number,division,class) where type='employee'

But performance is not great using index.Any suggestion here.

n1ql couchbase indexing query-optimization
3 Answers

The following query and index uses index order. You can verify by doing EXPLAIN and check (no "#operator": "Order" at the end)

CREATE INDEX idx_where ON employee (division, class, `number`) WHERE type ="employee";

SELECT * 
FROM employee
WHERE type= "employee" 
     AND `number`='123' 
     AND division='456'
ORDER BY class, `number`;

As projection contains *, it must fetch the documents that might be taking time.

user_60805360

The problem with your current index is that it does not cover the where, order by, or select portions of your query. For a possible immediate improvement, try adding the following index which covers the where clause:

CREATE INDEX idx_where ON employee (type, number, division);

We could also try improving on the above to cover the ORDER BY clause, but if you would always be expecting modestly-sized result sets (say < 100 records), then a manual hash sort might not be a big performance problem. Note that covering the select clause is probably out of the question given that you are doing a SELECT *.

user_18632290

In this case issue was with size of document was around 20 MB that is maximum document size to be indexes and hence index was unable to cover this up and performing poorly. We removed that document and it worked. Thanks everyone for suggestion.

user_76396810
Related