N1ql does not use index for "order by"

SQL++
21

Nice :slight_smile:. Pretty=false will get you more still.

22

umā€¦so there is no way to improve this query?

Even with pretty=false, itā€™s still between 900ms and 1.2 seconds, which is slower than the original query without new index

23

The index is helping with sorting.

The index and query can be improved. Can deleted and published be moved from the index keys to the index WHERE clause. Can any other fields be moved to the index WHERE clause.

If OFFSET is 0, the LIMIT can also be pushed down to the index.

24

Iā€™m not sure if it isā€¦response time is doubled with the new index.

Iā€™ve decided to drop n1ql and move to views at this point. Itā€™s not that n1ql is not good, but I think view is better choice for our use cases.

When it comes to counting and ordering, there is no benefit of using n1ql for our use cases since the performance is actually slower than view. Our use cases are not unique. They are normal pagination and ordering just like any other applications out there.

25

Noted. We are focused on use cases like yours, so we will keep at it. cc @keshav_m @prasad @vsr1

1 Like
26

I really appreciate all your help along the way :slight_smile:

Iā€™m sure we will be using n1ql for internal stuff though, just not user facing features as they require counting and ordering.

1 Like
27

Hi @moon0326, Please use this.

Your index definition and query has been modified in a subtle way. This will still give you the same result.

Subtle is the optimizer, wrong results, it not. :slight_smile:

 CREATE INDEX idx_by_username ON bucket(clientId,username,  `values`.deleted_at,  `values`.published, -STR_TO_MILLIS(meta.updatedAt)) WHERE form = 'creative';
 
select * from bucket
        where
        clientId = 'clientId'
        and form = 'creative'
        and username = 'username'
        and `values`.deleted_at is null
        and `values`.published = true
        and -STR_TO_MILLIS(meta.updatedAt) is not null
          order by clientId, username, `values`.deleted_at , `values`.published, 
                  -STR_TO_MILLIS(meta.updatedAt) 
     limit 30 offset 0
2 Likes
28

Your queries should now be returning results dramatically faster!

29

Thank you @keshav_m

I will try that tonight and report back :slight_smile:

30

Hi @keshav_m @geraldss

The query from @keshav_m worked finally :slight_smile:

As I learn more about n1ql, I feel like that n1q index is like a view. Only difference is that n1ql indexes live in memory. It looks like I need to create an index per use case just like a view. Thatā€™s different than my initial impression. I thought n1ql indexes can be re-used just like other traditional RDMS and have good performance. Iā€™m aware that n1ql indexes can be re-used, but then the response time is just not there at least for my use cases.

Thank you both of you. I will keep exploring, but I think we are still going back to view for now.

2 Likes
31

Iā€™m glad it all worked outā€¦

There are slight, subtle differences between indexes in RDBMS and Couchbase.
But, theyā€™re very close to RDBMS indices than a map-reduce view/materialized view.

BTW, you can see Couchbase Connect lifestream here.
http://info.couchbase.com/Connect16_Livestream_Registration.html