CODEWITHSUNDEEP
couchbasesql++
Bifrost
Bifrost

Reputation: 427

Couchbase Secondary index on filter

I am working with Couchbase 6.0. I know that we can create Secondary index on a filter.

So I have created a index like

CREATE INDEX idx_zipcode
       ON userbucket(zipcode)
       WHERE status = "active";

I have a question here:

Can I create an index on a filter clause if field is dynamic.

Something like this

CREATE INDEX idx_zipcode
       ON userbucket(zipcode)
       WHERE status = ? ;

Second question is,

which one is better in terms of performance:

Single index on 2 fields

CREATE INDEX idx_1 ON userbucket('fname','lname')

or

Separate Index on each field

CREATE INDEX idx_1 ON userbucket('fname')
CREATE INDEX idx_2 ON userbucket('lname')

Upvotes: 2

Views: 117

Answers (1)

Rizwan
Rizwan

Reputation: 2437

No we can not create the index with the dynamic clause mentioned accepting a bind variable.

However, when it is sure that the status and zipcode are part of predicates and dynamic in nature the index like below would be handy.

CREATE INDEX idx_zipcode_status  ON userbucket(zipcode, status);

Refer Couchbase Index Creation Blog - right performance.

Regarding the second query the same principle applies as

Index selection for a query solely depends on the filters in the WHERE clause of your query

Secondary Composite index is also okay when you have both or leading columns in your query.

CREATE INDEX idx_1 ON userbucket('fname','lname')

The above index would be exploited by queries like:

SELECT * FROM userbucket WHERE fname= 'fnam' AND lname= 'lnam';

Upvotes: 3

Related Questions