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."
}
],
"status": "fatal",
"metrics": {
"elapsedTime": "756.706µs",
"executionTime": "726.34µs",
"resultCount": 0,
"resultSize": 0,
"errorCount": 1
}
}
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?
If you use partial index (index where clause) index will have entries when index condition is true. Query where clause must have subset of index condition and condition on index leading key otherwise the index will not qualify.
In your case you can try this.
CREATE INDEX myTestIndex ON myTestBucket(type, name) WHERE type=’abc’ ;
SELECT * FROM myTestBucket WHERE type=’abc’ AND (name=’x’ OR name=’y’ OR name=’z’);
Example: If query has type = “pqr”, the index will not qualify because index doesn’t have entries for type =“pqr”.
Thanks for the response.
90% of my documents in the bucket are of type “abc” and my requirement is that I want to create index based on different values of name and other fields in the documents for the type “abc”. Having the generic index like CREATE INDEX myTestIndex ON myTestBucket(type, name) WHERE type=’abc’ ; makes my query to have all values of name and other fields. I guess the Index should be constructed based on all required fields like “WHERE type=’abc’ AND (name=’x’ OR name=’y’ OR name=’z’)” and query should be generic like SELECT * FROM myTestBucket USE INDEX (myTestIndex) ; or SELECT * FROM myTestBucket USE INDEX (myTestIndex) WHERE type=’abc’ AND name=’x’;.
CREATE INDEX myTestIndex ON myTestBucket(type, name) WHERE type=’abc’ ;
SELECT * FROM myTestBucket USE INDEX (myTestIndex);
Above query will not able to use myTestIndex, due to the following reason.
If the leading index key (type) is MISSING in the document indexer will not index that document.
But query doesn’t have any predicate (on leading index). So query needs to give all the documents even type is MISSING. So index will not qualify( non qualified index is used that result in wrong results), will not be used in that case it uses primary index.
If you want you can do this.
CREATE INDEX myTestIndex ON myTestBucket(type, name);
SELECT * FROM myTestBucket USE INDEX (myTestIndex) WHERE type IS NOT MISSING;
SELECT * FROM myTestBucket USE INDEX (myTestIndex) WHERE type=’abc’ ;
SELECT * FROM myTestBucket USE INDEX (myTestIndex) WHERE type=’abc’ AND name=’x’;.