Couchbase bucket and indexes

Couchbase Server
1

I am getting the error message saying that there needs to be an index for the query to execute.

image
image1347×396 22.6 KB

When I click on Advise, it says ‘No secondary index recommendation at this time, a primary index may apply.’

I have read in one documentation that in Prodn Environment one should not be using a primary index.
Any idea which index needs to be added to make this query work?

Regards

2

Secondary indexes only apply when there are filters (Ref: https://docs.couchbase.com/server/current/guides/select-index.html). In this case if you:

CREATE INDEX ix_type ON DataExchange(type)

and add a filter:

SELECT DISTINCT type FROM DataExchange WHERE type IS VALUED

it should then be supported by the index.

HTH.

1 Like
3

@dh
I have one more question. Why do we need to create a primary index or a secondary index for a query to run and execute successfully?
For example, In SQL Server when we create a table and add a few rows we are able to run the select statement, however, that is not the case with Couchbase.
Please can you shed some light on this Couchbase behavior?

Regards
JS

4

In a standard RDBMS you will be using a table scan for data access until you create indices.

In current (up to 7.1 at the time of writing) Couchbase releases the approximate equivalent of a table scan is a primary index scan - with benefits (you can control whether or not inefficient whole collection scanning is available, etc.) and drawbacks (space for and maintenance of the primary index, etc.).

In upcoming releases there will be a closer table scan equivalent: https://issues.couchbase.com/browse/MB-52184

There are various reasons for this difference to a traditional RDBMS including things like the distributed service architecture in Couchbase vs the monolithic process of a typical RDBMS.

Needless, we’d always encourage correct indexing for optimal performance and lowest processing overhead. We do not recommend a primary index in a production environment largely owing to the overhead of fetching all documents. Typically a primary index would be used only in a development environment (small data quantities) until the SQL++ is stabilised whereupon appropriate secondary indices can be created that will be pushed to the production environment.

HTH.