How to create index for "IN" statement

SQL++
1

Hello,

I have a query that looks like this:

SELECT META().id
FROM `data`
USE INDEX (pending_suggestions USING GSI)
WHERE META().id LIKE 'suggestion/%'
AND $uid in friendIDs

friendIDs is an array of strings. I tried to speed up this query using this index

CREATE INDEX pending_suggestions ON data(friendIDs)
WHERE (META().id LIKE 'suggestion/%')
USING GSI

but it is still very slow (2s for a bucket of 200000 docs of which 20000 fulfill META().id LIKE ‘suggestion/’). Looking at the output of EXPLAIN, the index isn’t really used for the AND $uid in friendIDs part of the query. Is there a way to index an array such that an IN query is faster?

Thank you in advance!
foobar123

2

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

CREATE INDEX pending_suggestions ON data( DISTINCT friendIDs)
WHERE (META().id LIKE 'suggestion/%');

SELECT META().id
FROM `data`
WHERE META().id LIKE 'suggestion/%'
AND  ANY v IN  friendIDs SATISFIES v = $uid END;
3

Thank you very much, works perfectly! :slight_smile: