Single Index to cover different sort fields (both asc & desc)

SQL++
,
1

Is it possible to handle all 4 below N1QL queries in a single index? Need to sort by OriginalWidth (asc & desc) or OriginalHeight(asc&desc) - based on user input.

--Query 1

select Id
from `QA-ImageManagement`
Where Type = 'Image'
order by OriginalWidth
limit 10
offset 0

--Query 2

select Id
from `QA-ImageManagement`
Where Type = 'Image'
ORDER BY OriginalWidth desc
limit 10
offset 0

--Query 3

select Id
from `QA-ImageManagement`
Where Type = 'Image'
ORDER BY OriginalHeight
limit 10
offset 0

--Query 4

select Id
from `QA-ImageManagement`
Where Type = 'Image'
ORDER BY OriginalHeight desc
limit 10
offset 0

This is just an example. My actual query has around 7 sort fields (both asc& desc - based on user input). Any help is highly appreciated. Thank you.

2

You can use single index to service all the queries.
Only one matched query can use Index order all others queries need to explicit sort that means it need to produce all possible values before even apply pagination.

3

Hi @vsr1

I tried the below index, Query1 looks fine but Query 2,3,4 takes more time to get the results. Am I missing something? could you please give me an example of an index that suits my need, please?

CREATE INDEX idx_ImageSearch ON QA-ImageManagement(OriginalWidth,OriginalHeight) WHERE (Type = “Image”)

4

That is exactly what i said earlier. It is not possible to use index order for sort.

First your query has only single predicate on Type it must be leading part of index key.

CREATE INDEX `idx_ImageSearch` ON `QA-ImageManagement` 
( Type, `OriginalWidth` , `OriginalHeight`, Id ) WHERE ( `Type` = "Image")

Index has entries that sorted by Type, with in Type it sorted by OriginalWidth, with in Type,OriginalWidth, sorted by OriginalHeight. Like dictionary words.

select Id
from `QA-ImageManagement`
Where Type = 'Image'
order by OriginalWidth
limit 10;

Above query and index combination can use can use index order and produce 10 results quickly because it can use index order.

select Id
from `QA-ImageManagement`
Where Type = 'Image'
order by OriginalHeight
limit 10;

Different query with same index can’t use index order. Index gives results Type =“Iimage” order by OriginalWidth, But query needs order by OriginalHeight. So it needs to resort before apply limit 10. If there are 100K items of Type = “Image” it needs to produce all 100K because last document might be first in the results?

5

So, I will not able to achieve a similar execution time for all four queries, using a single index? (as the index order is different from the sort order of Query 2,3,4).

Query 1 - 30ms execution time
Query2,3,4 - Takes 1 min as the doc count is more (for resort to happen).

6

Not with same index for pagination. You can explore https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/