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

Is this article helpful? 

Tell Fauna how the article can be improved:
Visit Fauna's forums or email docs@fauna.com

Thank you for your feedback!