Couchbase 4.5 Order by diffrence in performnace for ASC and DESC even query covered

Couchbase Server
1

ASC Query good response (60ms)

Select META().id from default where name is not missing and CONTAINS(name,‘for’) order by name ASC LIMIT 30

DESC query (538 ms – 10 times slower then ASC)

Select META().id from default where name is not missing and CONTAINS(name,‘for’) order by name DESC LIMIT 30

2

ASC is able to use the index order. Currently, our indexes do not implement DESC order, even though the CREATE INDEX syntax provides it. Therefore, DESC performs an explicit sort. Hence the difference in performance.

You can convert a DESC sort to an ASC by using a functional index on the negation or inverse of your sort field.

3

HI @geraldss

If sort is by numeric not sure inverse of index field do trick, can you please share sample query for this.

One more query if Index is already sorted by Order then DESC also should be able use same index ordering bottom-up approach

4

Here is an example using age. The result is sorted by age in DESC order.

CREATE INDEX neg_age ON mybucket(-age, age);

EXPLAIN SELECT age
FROM mybucket
WHERE -age iS NOT NULL
ORDER BY -age ASC;
5

Hi @geraldss

With this suggestion i will end with duplicate fields every where , for Webapps every fields should be sortable asc/desc.

Not sure the reason of not adding DESC to index but this is must to have feature

6

Yes, we will be adding DESC to index. That is why the syntax provides it.

7

Hi @geraldss

Thank you
.

Is DESC is planned with CB 4.5

8

Hi,

Not 4.5. But I verified that in 4.5, you do not need to duplicate data. The following example will use a covering index.

create index neg_abv on `beer-sample`(-abv);

explain select -(-abv) from `beer-sample` where -abv is not null order by -abv limit 5;

This is a workaround for 4.5.

9

@geraldss

Can you please suggest the same scenario if its string column

Alternate options looks okay for numeric fields but may be instead of null not missing to be used with index creation to have zero value if null.

10

Hi,

NULLs are ok, because negation preserves the NULLs.

This approach will not work for strings. For strings and complex types, we will need to implement DESC in our indexer. Or we would need to provide a STRING_NEG() function.

11

@geraldss

Okay

Can you please add some option for DESC order in CB 4.5 as DESC is very important

As current implementaion of DESC order by is very slow for 30K records taking almost 700ms

12

In my case ORDER BY is very very slow :frowning: . Kindly suggest something I want to apply it on Date column which format is “timestamp”: “2016-04-14 19:03:05”. Should I apply index or change date format or any thing else .

I also tried for SELECT *, meta(DEFAULT).id id FROM Test_mysql_import ORDER BY id LIMIT 5 OFFSET 0;

but no result no error :frowning: up to 180 sec.

Thanks in advance.

13

Hi @pratyushtty,

Can you post your exact index and query.

14

HI @geraldss

4.5 EE still not suitable for data where every field is sortable as adding all fields in covering index not possible

order by is very very slow if query not using covering index its very worst for ordering 70K records no where near to RDBMS

One more observation joins even worst as right side only uses covered index left side its doing key value fetch and if we put order by along with join its more worst

15

Are you using index order? Ascending or descending? If you post your queries, we can help identify the right set of indexes. For ordering, they do not necessarily need to be covering indexes.

16

hi @geraldss

here is my query

INDEX is on groupid,user,entity

select meta(a).id from test a join test b on keys (a.entity) where a.groupid=1 and a.type=‘FIRST_FILTER’ and b.type=‘SECOND_TYPE’ and ARRAY_CONTAINS(b.users,a.user) order by a.totturnover

i have 60 attributes on alias a test which are sortable (tot records left side matching groupid=1 70K)

17

Try the following exactly.

CREATE INDEX idx_totturnover ON test( groupid, type, totturnover );

EXPLAIN SELECT META(a).id
FROM test a JOIN test b ON KEYS (a.entity)
WHERE a.groupid=1
AND a.type='FIRST_FILTER'
AND a.totturnover IS NOT MISSING
AND b.type='SECOND_TYPE'
AND ARRAY_CONTAINS(b.users,a.user)
ORDER BY a.groupid, a.type, a.totturnover
;
18

Hi @geraldss

tried above option but still same slow if i add order by even in same index order

19

Hi @krishnasahu2016,

You are not providing enough information. You say that all the queries and indexes have the same performance, regardless of the query and index definitions.

(1) That is not likely, unless there is something wrong with your environment.

(2) Just saying everything is slow does not provide any information we can build on.

Can you run the exact index and query I sent. Can you post the output of EXPLAIN. Can you post the metrics from running the query.

20

Hi @geraldss

My working environment does not allow me to load data on any forum

But pls try below in ur working env you will face slowness

ID1 (like this with groupid 1 having 100K documents)
{
groupid:1
type:‘FILTER_1’,
entity:ENT1,
numbericvalue1:585,
numericvalue2…numericvalue60
}

ENT1( this id refered in above document)
{
users:[‘user1’,‘user2’…‘user1000’]
type:‘filter2’
}

now we need to filter by groupid 1 (Matching 100K documents) and sort by any column (60 columns) with join on entity attribute with second document and for specific user

e.g.

select META(a).id from default a join default b on keys (a.entity) where a.groupid=1 and a.type=‘FILTER1’ and b.type=‘FILTER2’ AND ARRAY_CONTAINS(b.users,‘TEST1’) order by { any attribute of a} limit 30

Please suggest on indexes or data model to make this faster