Any tips for order by clause performance tuning?

N1QL
1

select * from abc order by id
This kind of n1ql will take about 500+ms in execute the query, but remove the order by clause, it reduced to 10+ms, is there some performance tuning tips for this?

2 
CREATE INDEX ix1 ON abc(id);
SELECT *
FROM abc 
WHERE id IS NOT MISSING
ORDER BY id;

https://blog.couchbase.com/create-right-index-get-right-performance/

3

You are correct, but the key is

id is not missing

with this sentence, it force use id as index search then everything fine.

4

Found another issue:
If order by id desc, will fall to 500+ms, any solution???

5 
CREATE INDEX ix1 ON abc(id DESC);
SELECT *
FROM abc 
WHERE id IS NOT MISSING
ORDER BY id DESC;
6

Thanks, that’s what I want. So if we want to use asc and desc, we should create 2 indexes, right?

7

Above link has all the details. If you are looking performance and query expose index order you need two indexes and must provide USE INDEX hint based on query ORDER BY. Otherwise single index will serve.

8

Thanks, this is really helpful.