Index's performance with arrays

SQL++
, ,
1

I have a big number of documents which contain an array with objects, saying all my documents contain array like this one:

  {
     type: 'metallic',
     name: 'a name for this category'
  },
  {
     type: 'glass',
     name: 'another name for this category'
  }
]```

I created an index for the select which gets all documents which don't contain object with type = metallic. in 'explain' I can see that this index is used, however the speed is like it doesn't use index. Here my index:

```create index test on bucket(producer, distinct array categories.`type` for categories in material.categories end, material.categories) where producer='specificProducer'```

and this is part of my select:
```and every categories.`type` in material.categories satisfies categories.`type` = 'metallic' end```

What is wrong with all of this? Maybe I should add something to 'where' clause in my index?
2 
create index test on bucket(distinct array categories.`type` for categories in material.categories end, material.categories) where producer='specificProducer';

SELECT ...... FROM ... WHERE  producer='specificProducer' AND 
ANY categories IN material.categories SATISFIES categories.`type` = 'metallic'  END

ANY or SOME is TRUE if the collection is non-empty and at least one element matches.

EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.

ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.

Note: EVERY is TRUE if the collection is empty, In this case if array index key is not leading key it will choose index but it may do full scan of index. If array index is leading it may not choose index because empty array elements results MISSING and indexer will not index leading MISSING keys and but query need to return results.

https://blog.couchbase.com/making-the-most-of-your-arrays-with-array-indexing/

1 Like