Error with SELECT STMT and Index

SQL++
1

I have a straightforward query:

"select xrefId, CLSTR, CLUSTER_NAME, SHORTENED_NAME, "
        + "PRMRY, SET_NUM, TYPE from myBucket "
        + "where _class=\"com.package.model.ClusterCodeXref\"

And a corresponding secondary index (I’m not allowed to create a primary index on this bucket for internal reasons):

Definition: CREATE INDEX `idx_xref` ON `myBucket`(`xrefId`,`CLSTR`,`CLUSTER_NAME`,
`SHORTENED_NAME`,`PRMRY`,`SET_NUM`,`TYPE`)

Yet when I run the query (using spring-data-couchbase), I get the following error:

"No index available on keyspace myBucket that matches your query. 
Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online."

Can someone explain what’s wrong?

2 
Leading key of Index needs to be part of the query predicate.

CREATE INDEX `idx_xref` ON `myBucket`(_class, `xrefId`,`CLSTR`,`CLUSTER_NAME`,
`SHORTENED_NAME`,`PRMRY`,`SET_NUM`,`TYPE`)

If you can also change query add xrefId IS NOT MISSING, if it accepatble

3

Thanks for your response, but I still get the same error…

4

Did you created new index. It should have worked.

CREATE INDEX `idx_xref` ON `default`(_class, `xrefId`,`CLSTR`,`CLUSTER_NAME`, `SHORTENED_NAME`,`PRMRY`,`SET_NUM`,`TYPE`);
EXPLAIN select xrefId, CLSTR, CLUSTER_NAME, SHORTENED_NAME, PRMRY, SET_NUM, TYPE from default where _class="com.package.model.ClusterCodeXref";
5

I didn’t create it on the default bucket - the document is not stored there. But yes, I recreated the index as you suggested. Explain plan gives me the same error that no index is available.

6

Can you post the exact definition and query. You can try in cbq shell or Web UI

7

I’ve been trying in both interfaces. Here’s the index:

CREATE INDEX idx_xref ON API_Resource_WEM(_class,xrefId,CLSTR,CLUSTER_NAME, SHORTENED_NAME,PRMRY,SET_NUM,TYPE) where _class=com.package.model.ClusterCodeXref using GSI;

And here’s the query:

SELECT xrefId, CLSTR, CLUSTER_NAME, SHORTENED_NAME, PRMRY, SET_NUM, TYPE
FROM API_Resource_WEM
WHERE (_class = “com.package.model.ClusterCodeXref”)

8

You are missing quotes in index where clause.

CREATE INDEX idx_xref ON API_Resource_WEM(_class,xrefId,CLSTR,CLUSTER_NAME, SHORTENED_NAME,PRMRY,SET_NUM,TYPE)
 where _class="com.package.model.ClusterCodeXref" using GSI;