GSI Indexes with 'WHERE' clause

SQL++
1

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 * frombeer-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?

2

CREATE INDEX Index_beer_name ON beer-sample(name, type) WHERE (type = beer) USING GSI

3

This will still need a PRIMARY INDEX to execute though right?

4

No, it will not need a PRIMARY INDEX at all.

5

I added this index: CREATE INDEX Index_beer_name ON beer-sample(name,type) WHERE (type = beer) USING GSI

Then tried to execute this query:
select * from beer-sample where type = “beer” and name = “Drie Fonteinen Kriek”

and I get this error:
[
{
“code”: 4000,
“msg”: “No primary index on keyspace beer-sample. Use CREATE PRIMARY INDEX to create one.”
}
]

6

Try CREATE INDEX … WHERE type = “beer”;

You are missing the quotes around “beer”.

7

Great, thank you.

This appears to do it:
CREATE INDEX Index_beer_name ON beer-sample(name,type) WHERE (type=“beer”) USING GSI;

1 Like