Couchbase Query Help

SQL++
1

Hi,
Currently I have created the following index in the bucket:

CREATE INDEX index_one ON mt_bucket('a','b','c','d','e','f','g','h','i','type') USING GSI

if I am using the following N1Q1 query:

select * from mt_bucket USE INDEX (index_one USING GSI) where type=1 and a='a' and b='b' and c='c' and d='d' and e='e' and f='f' and g='g' and h='h' and i='i'
CouchBase Server Query Response time is millisecond.

However For the Following Query, it takes a minute

select * from mt_bucket USE INDEX (index_one USING GSI) where type=1 and a in ['a1','a2','a3','a4'] and b in ['b1','b2','b3','b4'] and c='c' and d='d' and e='e' and f='f' and g='g' and h='h' and i='i'

What Should I do to optimize the query

2

Try using Couchbase 4.6. Cc @vsr1

3

Isnt there any other soln apart from updating couchbase
Currently I am using 5 Data Nodes, 1 Query Node & I1Index Node Machines.
Since I am running these machines in production version, I cannot update them so easily

Current Couchbase : Version: 4.1.1-5914 Enterprise Edition (build-5914)

4

Hi @ronagrawal,

Query had 2 IN clauses of 4x4 that will makes 16 combinations. @geraldss suggested it is optimized in 4.5.0 onwards. If can’t upgrade rewrite query UNION ALL( or UNION if don’t want duplicates).

5

Thanks @vsr1 and @geraldss
For now I will write the union query and then try to upgrade my production machines with minimum down time