Styjun

What is the performance, disk usage and speed of a compound index(with an array index) and a compound index with a separate array index?

Lets say I have a document with a structure similar to this

key1:value1,
key2:value2,
key3:value3,
key4:value4,
data:[
 value5,
 value6,
 value7,
},
key8:value8

From what I understand of Couchbase N1QL if I create an index and query similar to:

CREATE INDEX indx_data ON document(key1,key4, ALL DISTINCT ARRAY v FOR v IN data END

SELECT * FROM document WHERE key1=value1 AND key4=value4 AND ANY v IN data SATISFIES v=value5 END AND ANY v IN data SATISFIES v=value6

The first part of the index will be used on the query but with the second value for data(ANY v IN data SATISFIES v=value6) the index will not be used? So would it be better to have an index for key1 and key4 and then a separate index for the array to be able to compare it? Or is there a way to use the compound index including the array?

Also another question that just popped up:

It is better to have a document to index as I just presented or similar to:

key1:value1,
key2:value2,
key3:value3,
key4:value4,
data:
 key5:value5,
 key6:value6,
 key7:value7,
,
key8:value8

This question is also performance

Are you checking data array has value5 or value6 or must present both.

If you are looking for either value5 or value6 you should use as follows. The query use the index push all the three keys. You can Verify using EXPLAIN (check Spans)

ANY v IN data SATISFIES v IN [value5, value6] END

If you are looking for both value5 and value6 must present you must use as follows. The query use the index push all the three keys. But here two different index scans does Intersection (index row has only one value at a time) You can Verify using EXPLAIN (check Spans)

ANY v IN data SATISFIES v=value5 END AND ANY v IN data SATISFIES v=value6 END

If you don't want to intersection you can do following. The query use the index push all the three keys and value6 IN data filter applied post index scan

ANY v IN data SATISFIES v=value5 END AND value6 IN data