Optimizing order by clause having mixed sorting (both asc and desc on different fields)

SQL++
1

Hi, I have a query:

Select r.firstName, r.lastName,  r.genderCode, r.phoneNumber,  Case when r.verificationStatus = 'PENDING' and date_diff_str(clock_local(), pendingVerificationDate, 'day') > 5 then 'OVERDUE' else r.verificationStatus  end as status
From reporting r
where r.type = 'Person'
and departmentCode= 'HS' 
and date_format_str(startDate, '1111-11-11') between "2018-03-01" AND "2018-03-20"
ORDER BY date_format_str(startDate, '1111-11-11') asc, isInternational desc, isOcrDirty desc, status asc LIMIT 10 OFFSET 0
;

I need the results in this particular order . Can anyone please suggest a covering index for this query that takes care of order by clause as well? I have tried a few indexes but it’s not working and query is taking time.

2

what is couchbase version

3

5.0.0-837 (Analytics DP4)

4

Analytical team may be able to help, cc @till.

5

I am not using analytics query service for this. Using regular query workbench.

6

For N1QL You can use the following index.

CREATE INDEX ix1 ON reporting(date_format_str(startDate, "1111-11-11"),sInternational desc, isOcrDirty desc, status, firstName, lastName, genderCode, phoneNumber, verificationStatus , pendingVerificationDate ) WHERE type = "Person" AND departmentCode= "HS";

Make sure results of CASE is correct. If not add LET xxx =[verificationStatus , pendingVerificationDate] for query and try it.

7

Hi @vsr1, ix1 is not being used in the query even after specifying it in ‘use index’ clause.
I crated following index which is working only when ‘status’ is not being mentioned in the order by clause. Please take a look at the stats:

CREATE INDEX ix2 ON reporting(date_format_str(startDate, "1111-11-11"),isInternational DESC,isOcrDirty DESC,verificationStatus,pendingVerificationDate,firstName,lastName,genderCode,phoneNumber) WHERE ((type = "Person") and (departmentCode = "HS"))

Query 1 (without ‘status’ in order by clause) is taking less than 100 ms.
Query 2 (With ‘status’ in order by) is taking approx. 2-3 seconds.

Tried the same query with let clause as well and it gave same results.

8

status is missing in your index ix2 before verificationStatus.
Please note Index selection only depends on query WHERE clause. If there multiple indexes any index can be used. Later it decides if query can use index order. You can specify USE INDEX to force right index.

9

I have a question though. Why should ‘status’ be in index when it is a computed field in the select query and it’s an alias given to the verificationStatus? I mean as it’s not part of the main document, it will not be of any use in the index itself.
See what I am trying to do is, to show the status ‘OVERDUE’ in case the verificationStatus is ‘PENDING’ and it has crossed 5 days since the pendingVerificationDate. else, whatever the value verificationStatus holds should be shown. But I want ‘OVERDUE’ to be shown before other statuses after applying other sorts. (rest verificationStatuses are alphabetically greater than ‘O’)

10

Did not see that. In that case, query can’t use the index order. It needs produce all qualifying items and do the sort.

11

Okay. Guess I’ll have to rethink the logic. Thanks a lot for your time.