Array indexing in Couchbase server 4.5

Couchbase Server
1

Hi,

I’m using Couchbase server 4.5. I believe it supports array indexing. I am trying to find count / list of document, based on an array element present in document. Following is my document structure:

“cms”: {
"_active": true,
"_type": “Product”,
“attributes”: {
“101”: “32 inch”
},
“brand”: “1001”,
“categories”: [
“17”,
“15”
],
},
“id”: “Product::SMA.TES.10000172”
}

I have around 1 million such documents and I need to find some documents based on a certain category id present in categories array.

I executed following query.

SELECT count(meta().id) FROM cms WHERE _type = “Product” AND _active = TRUE AND ANY category IN categories SATISFIES category = “17” END;

It took around 1 min 20 sec to get the result on cbq shell. To reduce query time i also created following index:

CREATE INDEX prod_cat ON cms(_type,_active,(distinct (array V for V in categories end))) WHERE ((_type = “Product”) and (_active = true));

On using EXPLAIN with above SELECT query, it shows that it is using this INDEX.

But the query executing time is same as earlier. I want to understand am i creating index correctly or am i missing something here.

I have to reduce this time anyhow as this is affecting my application. Please guide.

Thanks in advance

2

Hi @amar1991

Can you try changing the create index statement to the following :
CREATE INDEX cat ON cms(distinct (array V for V in categories end),categories) WHERE ((_type = “Product”) and (_active = true));

and also changing the query to
explain SELECT count(*) FROM cms WHERE _type = “Product” AND _active = TRUE AND ANY V IN categories SATISFIES V = “17” END;

OR

explain SELECT count(1) FROM cms WHERE _type = “Product” AND _active = TRUE AND ANY V IN categories SATISFIES V = “17” END;

and check if it reduces the time.

Thanks