How do I create an index for an embedded object in Couchbase?

Couchbase Server
1

I have 1 million documents in the bucket.

I want to run this query:

SELECT * FROM bucket WHERE type = ‘toy’ AND material = ‘plastic’ AND color = ‘red’ AND weight = ‘200gr’ AND height = ‘5cm’ AND width = ‘15cm’

“attributes” is an object embedded within the document.

Example:

“type”: “toy”,
“other key”: “other value”,
“other key”: “other value”,
“attributes”: {
“material”: “plastic”,
“color”: “red”,
“weight”: “200gr”,
“height”: “5cm”,
“width”: “15cm”
}
“other key”: “other value”,
“other key”: “other value”

How do I create the index in Query Workbench to get the fastest possible response time?

2

Just on the query that you are running, are you always doing a SELECT * or do you only need to return the values from certain fields?

It’s easy enough to give suggestions to cover the WHERE statement, but if you’re doing a SELECT * then you will take a performance hit as the whole document needs to be retrieved.

3

Thanks for the really fast answer. I only need to return the value from certain fields. The SELECT * was only an example. I’m more interested in how to setup a proper index.

4

There are a couple of ways to do this:

You could create an index per type. This index will only work for “toys”, you need to create other indexes for other types

CREATE INDEX toys ON bucket (attributes.material, attributes.color, attributes.weight, attributes.height, attributes.width) WHERE type = “toys”

More generic index that will cover all types.

CREATE INDEX toys ON bucket (type, attributes.material, attributes.color, attributes.weight, attributes.height, attributes.width)

If you are returning any other fields just add them to the end of the index. Maybe try some of these and post an EXPLAIN if they aren’t performing.

5

Thanks a lot clinton1ql. It works and the response time is very fast. I have learned a lot. I am used to mysql so this is completely new to me. I’t been a while since I was this exited about a database :slight_smile: