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 (type
= beer
) USING GSI
With these two indexes I’m able to query on type
and name
like in the below query:
select * from
beer-samplewhere 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?