Reputation: 307
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.
Upvotes: 2
Views: 431
Reputation: 307
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.
Upvotes: 0
Reputation: 7414
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.
Upvotes: 2
Reputation: 522561
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 *
.
Upvotes: 1