How to create a Index which will allow to return top x docs based on index

SQL++
1

I have in my bucket a _type called batch_ino where i store summary info of every batch i run. All works fine and as expected but when i try to get the last 5 batches based on the Start of bach which is a time stamp it takes a long time.
Here is the query i use

SELECT d.*
FROM rets AS d
WHERE d._type = “batch_info”
ORDER BY d.Start
LIMIT 5

even so the filed Start and _type are covered via Index it has no impact on the sort.

CREATE INDEX adv_Start ON rets(Start) WHERE (_type = 'batch_info')

What index do i need to create to be able to get the Top 5 ASC or DESC based on Start Time ?

2

Cocuhbase indexes doesn’t do reverse scan MB-19917 to support index order. You need to create two different indexes and if needed specify as USE INDEX hint.

CREATE INDEX adv_Start_asc ON rets (`Start`) WHERE (`_type` = 'batch_info');

SELECT d.*
FROM rets AS d
WHERE d._type  = "batch_info" AND d.`Start`  IS NOT NULL
ORDER BY d.`Start` 
LIMIT 5;

CREATE INDEX adv_Start_desc ON rets (`Start` DESC) WHERE (`_type` = 'batch_info');

SELECT d.*
FROM rets AS d
WHERE d._type  = "batch_info" AND d.`Start`  IS NOT NULL
ORDER BY d.`Start`  DESC
LIMIT 5;
3

Something is wrong in the syntax as i get

syntax error - at SELECT",

4

Add ; at the end of CREATE INDEX.

5

That worked but really didn’t improve the query performance. also tried to specify index but same

SELECT d.*
FROM rets AS d
USE INDEX (adv_Start_desc USING GSI)
WHERE d._type = "batch_info"
ORDER BY d.`Start` DESC
LIMIT 5

image
image1109×155 16.4 KB

6

You need to add AND d.Start IS NOT NULL in your predicate.
Without that adv_Start_desc doesn’t qualify ( As you see in the plan it used adv_ret_type index).

if you don’t want to do that change the index
CREATE INDEX adv_Start_desc ON rets (_type, Start DESC) WHERE (_type = ‘batch_info’);

7

Ok that did the trick and works without use of Index. I thought if i use Use Index it always would use the index specified.