N1QL without primary index

SQL++
1

Can I do N1QL query with only view index ?

This post said "It is the basic requirement to be able to use the query language on your data".

This post said it’s not.

Here’re my sql list:

//create view index
CREATE INDEX idx_tp_app_mt ON default(tp,app,mt) WHERE tbl == 'dv';

//query on view index
EXPLAIN SELECT * FROM default USE INDEX(idx_tp_app_mt USING VIEW)WHERE tp = 'i'

When I try query with only one view index. the server returns

[
  {
     "code": 4000,
     "msg": "No primary index on keyspace default. Use CREATE PRIMARY INDEX to create one."
   }
]

What I need is doing N1QL query without primary index (it use too much resource).

Thanks

2

First, your CREATE INDEX statement must have USING VIEW.

Second, your SELECT statement should select specific fields from the index, and not SELECT *.

3

hi geraldss, thanks for your reply.

First, yes, I should change the statement, adding “USING VIEW”
Second， I believe SELECT * is right.

Turns out the root cause is " WHERE tbl == 'dv' "

When I change the “create index” statement as below:
CREATE INDEX idx_tp_app_mt ON default(tp,app,mt) USING VIEW;
Then everything works well (including "SELECT *").

any suggestion ?

4

Sounds good. If you select specific fields instead of *, you can also get covering indexes with Couchbase 4.1.

You should also consider GSI indexes. Anyway, all these approaches work. It’s just tradeoffs, which you should try out using EXPLAIN and the metrics of your queries.