Order by works very slow

SQL++
1

Hi there,

I have around 500.000 data in my bucket. I have index like this

create index geofenced on myTest(appId, geofenceBased) WHERE deleted = FALSE AND _class = com.push.data.entity.PushEntity

When I query like this:

select myTest.* FROM myTest
WHERE _class = “com.push.data.entity.PushEntity” AND
(appId = “white” AND deleted = FALSE AND geofenceBased = FALSE )
LIMIT 20 OFFSET 0 , it works very fast around 500k items. it uses my index however when it comes to order by query like this:

select myTest.* FROM myTest
WHERE _class = “com.push.data.entity.PushEntity” AND
(appId = “white” AND deleted = FALSE AND geofenceBased = FALSE )
ORDER BY createdAt DESC LIMIT 20 OFFSET 0

it works around 45 seconds. How can I achieve this ? It does not sound like it is normal, it can not order by createdAt date between 20 items ?

Thanks for help !

3 
create index geofenced on myTest(appId, geofenceBased, createdAt DESC) 
WHERE deleted = FALSE AND _class = "com.push.data.entity.PushEntity";

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

1 Like