How to create Indexes for double INNER JOIN query

SQL++
1

How do I figure out what type of indexes to create for the following query:

SELECT META(`contacts`).id, `contacts`.*
FROM `somebucket` contacts
INNER JOIN `somebucket` locations ON `locations`.id IN 
(ARRAY location.id FOR location IN `contacts`.locations END)
AND `locations`.type = "location"
AND `locations`.tenant = "tenant::sometenant"
INNER JOIN `somebucket` users 
ON `locations`.territory IN `users`.territories
AND `users`.type = "user"
AND `users`.tenant = "tenant::sometenant"
WHERE `contacts`.type = "contact"
AND `contacts`.tenant = "tenant::sometenant"
AND META(`users`).id = "sometenant::user::36da3a63-a57c-405c-8f2a-6570de058f75"

I have read a few articles and still do not get how I should proceed. I have tried to create several indexes with mixed results. I would like the indexes, but I want to understand the process.

Thanks

2

Couchbase JOINS are LEFT to RIGHT specified in JOIN clause.
If you have selective predicate move that left most. In your case contacts already have document key. Move that left most and specify as USE KEYS vs index scan.

Then each right side JOIN check all JOIN predicates (including WHERE clause, ON clause) with all previous left once and try to create index on those.
The following article explains in depth examples https://blog.couchbase.com/ansi-join-support-n1ql/

Explore Index Advisor https://query-tutorial.couchbase.com/tutorial/#55 . It advices on query given will not rewrite and advice.

The following rewritten query with indexes should perform better

SELECT META(`contacts`).id, `contacts`.*
FROM `somebucket` AS users USE KEYS ["sometenant::user::36da3a63-a57c-405c-8f2a-6570de058f75"]
INNER JOIN `somebucket` AS locations ON `locations`.territory IN `users`.territories
                                        AND `locations`.type = "location"
                                        AND `locations`.tenant = "tenant::sometenant"
INNER JOIN `somebucket` AS contacts ON ANY l IN `contacts`.locations SATISFIES l.id = `locations`.id END
                                        AND `contacts`.type = "contact"
                                        AND `contacts`.tenant = "tenant::sometenant"
WHERE `users`.type = "user"
       AND `users`.tenant = "tenant::sometenant";

CREATE INDEX ix1 ON `somebucket` (tenant, territory) WHERE type = "location";
CREATE INDEX ix2 ON `somebucket` (tenant, DISTINCT ARRAY l.id FOR l IN locations END) WHERE type = "contact";
3

Thanks a bunch. You turned what was a 1 minute 50 second query into a 30 ms query. You have some mad skills. I will check out the articles you linked to. Hopefully I can begin to understand the process of optimizing these queries and building the correct indexes.

Thanks again, you have an awesome rest of your day.