I’m trying to figure out the best way to use GSI indexes with a WHERE clause.
Using the beer-sample bucket as an example:
If I create the below indexes:
CREATE INDEX Index_beer_type ON beer-sample(
type) USING GSI
CREATE INDEX Index_beer_name ON beer-sample(
name) WHERE (
beer) USING GSI
With these two indexes I’m able to query on
name like in the below query:
select * frombeer-sample
where type = "beer" and name = "Drie Fonteinen Kriek"
However, when I ‘explain’ the above query; it is only using the ‘Index_beer_type’ index and doesn’t make use of the ‘Index_beer_name’ index at all.
What is the best practice here? How do I make use of an index that has a ‘where’ clause?
Is a “primary index” required when using a ‘where’ clause in an index?