Multi-collection indexes

Overview

A multi-collection index is an index which includes documents from more than one collection.

Indexes which cover multiple collections may be less performant than those which cover a single index. If possible, it’s a better practice to organize your collections and queries so that multi-collection indexes are not necessary.

Example

For demonstration purposes, let’s create two collections named fruit and flowers.

const schemas = []
const collections = [
  'flowers', 'fruit',
]
collections.forEach((name) => {
  schemas.push(q.CreateCollection({ name: name }))
})
client.query(
  q.Do(schemas, 'Collections created!')
)
.then((res) => console.log(res))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

Collections created!

Query metrics:

bytesIn: 135
bytesOut: 35
computeOps: 1
readOps: 0
writeOps: 2
readBytes: 822
writeBytes: 656
queryTime: 46ms
retries: 0

The following example adds some documents to the fruit collection:

const schemas = []
const fruitItems = [
  { type: 'apple', color: 'red' },
  { type: 'banana', color: 'yellow' },
  { type: 'mango', color: 'green' },
]
var counter = 1

fruitItems.forEach((item) => {
  schemas.push(q.Create(q.Ref(q.Collection('fruit'), counter), { data: item }))
  counter++
})
client.query(
  q.Do(schemas, 'Fruit items created!')
)
.then((res) => console.log(res))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

Fruit items created!

Query metrics:

bytesIn: 399
bytesOut: 35
computeOps: 1
readOps: 0
writeOps: 3
readBytes: 42
writeBytes: 1,006
queryTime: 47ms
retries: 0

Now let’s add some documents to the flowers collection:

const schemas = []
const flowers = [
  { type: 'rose', color: 'red' },
  { type: 'violet', color: 'blue' },
  { type: 'carnation', color: 'red' },
]
var counter = 1

flowers.forEach((flower) => {
  schemas.push(q.Create(q.Ref(q.Collection('flowers'), counter), { data: flower }))
  counter++
})
client.query(
  q.Do(schemas, 'Flower items created!')
)
.then((res) => console.log(res))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

Flower items created!

Query metrics:

bytesIn: 405
bytesOut: 36
computeOps: 1
readOps: 0
writeOps: 3
readBytes: 42
writeBytes: 997
queryTime: 34ms
retries: 0

To make these two collections searchable by their color field, we can create an index which specifies both collections in the source field and color in the terms field.

client.query(
  q.CreateIndex(
    {
      name: 'fruit_and_flowers_search_by_color',
      source: [q.Collection('fruit'), q.Collection('flowers')],
      terms: [
        { field: ['data', 'color'] },
      ],
    },
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

{
  ref: Index("fruit_and_flowers_search_by_color"),
  ts: 1642632647330000,
  active: false,
  serialized: true,
  name: "fruit_and_flowers_search_by_color",
  source: [Collection("fruit"), Collection("flowers")],
  terms: [
    {
      field: ["data", "color"]
    }
  ],
  partitions: 1
}

Query metrics:

bytesIn: 179
bytesOut: 408
computeOps: 1
readOps: 0
writeOps: 2
readBytes: 1,316
writeBytes: 1,343
queryTime: 64ms
retries: 0

The following example searches the index fruit_and_flowers_search_by_color for documents with the string red in the color field.

client.query(
  q.Map(
    q.Paginate(q.Match(q.Index('fruit_and_flowers_search_by_color'), 'red')),
    q.Lambda('item', q.Get(q.Var('item')))
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

{
  data: [
    {
      ref: Ref(Collection("flowers"), "1"),
      ts: 1648232012790000,
      data: { type: 'rose', color: 'red' }
    },
    {
      ref: Ref(Collection("fruit"), "1"),
      ts: 1648232012610000,
      data: { type: 'apple', color: 'red' }
    },
    {
      ref: Ref(Collection("flowers"), "3"),
      ts: 1648232012790000,
      data: { type: 'carnation', color: 'red' }
    }
  ]
}

Query metrics:

bytesIn: 151
bytesOut: 160
computeOps: 1
readOps: 0
writeOps: 0
readBytes: 0
writeBytes: 0
queryTime: 86ms
retries: 0