N1QL index without where clause

SQL++
1

I create the following index

CREATE INDEX idx_type ON test(type)

Then I run the following query:

Select distinct type from test

My query does not use the index, can you tell me what I’m doing wrong? Do I have to have a where clause in my index?

My main objective is to get a list of type fields from ALL documents in the bucket.

2

The index is fine, the issue is with the query.

Select distinct type from test

This query is saying check every document and report the type field, including documents that do not have the type field. As a result the index can’t be used as it only contains information about documents that have the type field.

Instead I suspect the query that should be used is

SELECT  DISTINCT type FROM default WHERE type IS NOT MISSING;

In this case it will report the list of type fields for all documents that has a type field, which means the index can then be used.

1 Like
3

Thanks, but I have one final question, would there be a difference between those 2 indexes

CREATE INDEX idx_type ON test(type)

CREATE INDEX idx_type ON test(type) and type is not missing

Why does the latter have a smaller disk footprint if both indices serve the same functionality?

4

CREATE INDEX idx_type ON test(type)

Couchbase GSI index only index the documents whose leading index key is NOT MISSING. If the leading index key is MISSING in the document those documents are not indexed. So both indexes have same disk foot print.

Select distinct type from test;

Above query must give the entry when type is MISSING, index doesn’t have that info due to that index has not chosen ( Index qualifies only when it has all the information. with out that result in wrong results and index will not be chosen).

2 Likes