I run the following query:
SELECT br.name AS brewery, b.name AS beer FROM `beer-sample` b JOIN `beer-sample` br ON KEYS(b.brewery_id) WHERE br.name=‘Aass Brewery’ AND br.type=‘brewery’ AND b.type=‘beer’
I’ve created indexes before:
CREATE INDEX beer_sample_brewery_id_type_idx ON beer-sample(brewery_id,type) WHERE (type = “beer”) USING GSI
CREATE INDEX beer_sample_name_type_brewery_idx ON beer-sample(name,type) WHERE (type = “brewery”) USING GSI
But these indexes are not used in the query. It is important to filter by fields from the brewery table.
How can I force existent indexes to work? What indexes should I create to optimize this query?
Couchbase version 4.0.0-4051 Community Edition (build-4051)