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

Couchbase Server
41

sorry not getting what changes i have to follow, currently clearly data missing

42

Hi @krishnasahu2016,

Regarding:
create index indx on test(all array grp for grp in grps end) where type=‘test1’ tot indexed dcouments 290K
and
create index indx on test(all array grp for grp in grps end,grps,users) where type=‘test1’ 254K documents

Can you check if the documents are being skipped as entire array size is exceeding the limit? The error message should be logged in indexer.log and would be like “array item too long. Skipped.”.

Thanks,
Prathibha

43

Hi @geraldss,

Sorry for late reply

Index is
Definition: CREATE PRIMARY INDEX #primary ON pks_mysql_import USING GSI

Document is like as

id
google

ĐĄontent
{
“clicks”: 0,
“ip”: “10.49.149.147”,
“keyword”: “Sample”,
“timestamp”: “2016-09-28 14:28:37”,
“title”: “http://google.com”,
“user”: “admin”
“url”: “http://google.com”
}

My query is “SELECT * FROM $bucket LIMIT $perpage OFFSET $offset” but I want to make it like as
"SELECT * FROM $bucket ORDER BY timestamp ASC LIMIT $perpage OFFSET $offset" but this is taking too long time.

Kindly consider my database is approx 90-100 Million

44

Hi, you cannot use primary index for performance.

45

Hi all,

i have evaluated same data set using some full text search engines wow taking 1/4th memory and disk space with data in sub-milliseconds with sorting.

Please improve order by and index performance also space usage for effectively use couchbase in production

46

Thx @geraldss , So what I should do in this case ? As I have already shared my query
SELECT * FROM $bucket ORDER BY timestamp ASC LIMIT $perpage OFFSET $offset. Should I create one more index ? if Yes then on which column to make it effective ? Is column name timestamp ?

47

How come you don’t have a WHERE clause?

48

I want to show all records for admin. so I am not using where clause.

49

Ok.

CREATE INDEX idx1 ON mybucket( timestamp );

50

Thx geraldss I try this. I just wan to know one more thing is there substitution of where 1=1 in CB like
Select * from mybucket where 1=1. (Mysql Query). Basically we use it in programing .To add condition dynamically

51

How long is the query taking now?

52

Hi geraldss, it’s not working.

select * from my_bucket LIMIT 5 OFFSET 15; ( “executionTime”: “36.261034ms”)

select * from my_bucket ORDER BY timestamp ASC LIMIT 5 OFFSET 15; (“executionTime”: “3.027551406s”).

While there is only for 46K records. Consider, as suggested I have already created index on timestamp.

53

Please always post the EXPLAIN as well.

54

Hi geraldss . Any thing is missing ?

55

Yes, the EXPLAIN is missing. Please post.

56

Dear Geraldss,
I want to show all records on single page for admin. For this purpose I am using ORDER BY .

SELECT * FROM $bucket ORDER BY timestamp ASC LIMIT $perpage OFFSET $offset

Above query is taking too much time. This is origin of my question .As suggested I have created index CREATE INDEX idx1 ON mybucket( timestamp ); But still it did not put any impact on execution time.

If I am using query without Order by it’s taking 36.2 ms
select * from my_bucket LIMIT 5 OFFSET 15; ( “executionTime”: “36.261034ms”).

If I ma using Order BY it is taking 3 sec
select * from my_bucket ORDER BY timestamp ASC LIMIT 5 OFFSET 15; (“executionTime”: “3.027551406s”).

while number of records in bucket are only 46000.

57

Sorry, I missed this earlier. Here you go.

SELECT * FROM my_bucket
WHERE timestamp IS NOT MISSING
ORDER BY timestamp ASC LIMIT 5 OFFSET;
58

Sorry Geraldss,

As suggested I executed following query

select * from my_bucket WHERE timestamp IS NOT MISSING ORDER BY timestamp ASC LIMIT 5 OFFSET 15;

It took more than 4 sec. So no Impact

59

Please post the EXPLAIN for this.

60

yes, I tried but still it’s taking lot of time. can i share plain text of EXPLAIN