What index should I create to optimize query with JOINs?


#1

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)


#2

This query would use an index on b.type.

In 4.0, only the first keyspace will be considered for index scans.