Covering index and select *

SQL++
1

I have a bucket with 4 fields
developer
date
action
items

and I created a covering index

Definition
CREATE INDEX covering_idx ON barfyUsage(developer,date,action,items)

as well as 4 secondary indexes on each field

maybe I am totally confused but do I need a primary index (I know bad) to be able to do a select without a where clause?

select date,action,developer,item from barfyUsage; or select * from barfyUsage work?

this is the error I am getting:
{
“errors”: [
{
“code”: 4000,
“msg”: “No index available on keyspace barfyUsage that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”,
“query_from_user”: “explain select date,action,developer,item from barfyUsage;”
}
]
}

and here is the explain

{
“errors”: [
{
“code”: 4000,
“msg”: “No index available on keyspace barfyUsage that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”,
“query_from_user”: “explain select date,action,developer,item from barfyUsage;”
}
]
}

I am still trying to learn couchbase indexes and could use a really good explanation of couchbase indexes if anyone knows of a good site …

2

Primary index is not recommended in production.
If you need to query without predicate only option is primary index.

JSON doesn’t have any schema and Couchbase buckets can have any document.
Couchbase secondary index only index the document when leading index is NOT MISSING in the document (other wise index can grow quickly). Due to that reason Index selection for query is based on query predicate. Index qualifies when leading index key is part of query predicate only.
Due to that reason if there is no query predicate only option is primary index.

If you are okay to get the documents with developer present only you can try this

select date,action,developer,item
 from barfyUsage WHERE  developer IS NOT MISSING;

https://blog.couchbase.com/n1ql-practical-guide-second-edition/
https://blog.couchbase.com/author/keshav-murthy/

3

thank you very much this was VERY helpful!