I have created a partial GSI index say CREATE INDEX myTestIndex ON myTestBucket(type, name) WHERE type=’abc’ AND (name=’x’ OR name=’y’ OR name=’z’);
myTestBucket has several documents with different values for type and name fields. In my application I have to query this index and get either all documents or few based on the value of name field.
In order to get all documents from myTestIndex I constructed below query.
cbq> SELECT * FROM myTestBucket USE INDEX (myTestIndex);
"msg": "No index available on keyspace myTestBucket that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online."
If I try with SELECT * FROM myTestBucket USE INDEX (myTestIndex) WHERE type=’abc’ AND (name=’x’ OR name=’y’ OR name=’z’); I will get all documents from myTestIndex.
Is it mandatory to have WHERE clause in query? Only by using the index name can I get all documents?
If I create one more index say CREATE INDEX myTestIndex__2 ON myTestBucket(type, name) WHERE type=’abc’ AND (name=’k’ OR name=’l’ OR name=’m’);
And now if I try to query the index EXPLAIN SELECT * FROM myTestBucket USE INDEX (myTestIndex__2) WHERE type=’abc’; it uses myTestIndex to get the documents instead of myTestIndex__2.
I was expecting query with USE INDEX should use the specified index with or without WHERE clause. Is there a way I can instruct my query to select specified index with or without WHERE clause?