Use indexes for more efficient queries

The previous Query on collections tutorial uses built-in Set methods that traverse the whole dataset to get the data you want. This tutorial shows you how to use indexes as a more cost-effective and performant alternative.

Indexes scale with your workload and give you predictable performance and cost. Effectively, an index is a lookup table. You define the fields you’re interested in, and Fauna indexes the documents that match your selection criteria. As an example of how this works, and the optimization you can realize if you define a bestFlavor index as follows,

CoffeeBean.definition.update({
  indexes: {
    bestFlavor: {
      terms: [{ field: "Species" }],
      values: [{ field: "Harvest_Year" }]
    }
  }
})

and then make the following query:

CoffeeBean.bestFlavor("Robusta").map(.Harvest_Year).toArray()

Fauna returns an array of harvest years for all Robusta species in the collection but Fauna didn’t read any documents because the term .Harvest_Year is covered by the index, which makes the query significantly less expensive compared to if the value .Harvest_Year wasn’t covered.

You declare indexes by name, which then populate the Collection indexes field with the terms and values fields set to what you want to index on. For defined values, all documents are indexed, including those that have a null value for the field. See Indexes definition.

Two indexes with the same shape, except for the id field specified last and ascending, are treated as the same shape. Effectively, including id as the last covered value is the same as not including it.

The following shows how to define, query, and manage indexes. The examples use FQL directly to create an index. You can also use the Dashboard or CLI schema management in keeping with best practices for defining schema:

  • To define an index using the Dashboard, select the database, and with the Collections menu item selected in the lower panel for the database, click the collection you want to index to expand the panel and display the Documents and Indexes menu items. Choose Indexes and click the CREATE INDEX button. Enter the index Name, Terms, and Values.

  • To define an index using the CLI, define the index in a .fsl schema file and push the file to Fauna. See Get started with FSL for more information.

Create an index and submit an indexed query

Using the CoffeeBean collection you created in the Populate the cookbook database tutorial, you should already have documents that you can index. Call CoffeeBean.all() to verify the documents and see the terms used as selection criteria in this tutorial. You can experiment with indexing by adding more documents with different data.

  1. Use the document update() method to create an index in the CoffeeBean collection. Give the index a name and define the terms and values fields you’re interested in. For this tutorial, declare an index that selects on the Species and Altitude fields, and name the index bestFlavor:

    CoffeeBean.definition.update({
      indexes: {
        bestFlavor: {
          terms: [{ field: "Species" }],
          values: [{ field: "Altitude.mean" }]
        }
      }
    })
    {
      name: "CoffeeBean",
      coll: Collection,
      ts: Time("2023-06-03T21:34:26.710Z"),
      indexes: {
        bestFlavor: {
          terms: [
            {
              field: "Species"
            }
          ],
          values: [
            {
              field: "Altitude.mean",
              order: "asc"
            }
          ],
          queryable: true,
          status: "complete"
        }
      },
      constraints: []
    }

    Notice that the bestFlavor index is added to the collection indexes field. Also, documents are returned in the default, ascending (asc) order of the values field.

  2. View the indexes you declared by querying the collection definition:

    CoffeeBean.definition

Get the indexed documents that match your selection criteria

This tutorial selects all documents matching a given species terms value and introduces you to the values range parameter.

  1. Query using the bestFlavor indexed, requesting all documents for the Arabica species grown at an altitude of 1500 meters and higher:

    CoffeeBean.bestFlavor("Arabica", { from: 1500 })
    {
      data: [
        {
          id: "366190711733747780",
          coll: CoffeeBean,
          ts: Time("2023-06-03T14:58:52.120Z"),
          Species: "Arabica",
          Owner: "Healthy Grounds",
          Country_of_Origin: "Guatemala",
          Harvest_Year: "",
          Quality_Parameters: {
            Aroma: 8.42,
            Flavor: 8.5,
            Balance: 8.42
          },
          Altitude: {
            unit_of_measurement: "m",
            mean: 1700
          },
          Best_of_Class: Date("2023-06-03")
        },
        {
          id: "366190504817197124",
          coll: CoffeeBean,
          ts: Time("2023-06-03T14:58:37.770Z"),
          Owner: "metad plc",
          Country_of_Origin: "Ethiopia",
          Species: "Arabica",
          Harvest_Year: 2014,
          Quality_Parameters: {
            Aroma: 8.67,
            Flavor: 8.83,
            Balance: 8.42
          },
          Altitude: {
            unit_of_measurement: "m",
            mean: 2075
          },
          Best_of_Class: Date("2023-06-03")
        }
      ]
    }

    At least two documents are returned that match the terms and values fields in order of ascending altitude.

  2. Using the values field range parameter further restricted the selection:

    CoffeeBean.bestFlavor("Arabica", { from: 1500, to: 1800 })
    {
      data: [
        {
          id: "366190711733747780",
          coll: CoffeeBean,
          ts: Time("2023-06-03T14:58:52.120Z"),
          Species: "Arabica",
          Owner: "Healthy Grounds",
          Country_of_Origin: "Guatemala",
          Harvest_Year: "",
          Quality_Parameters: {
            Aroma: 8.42,
            Flavor: 8.5,
            Balance: 8.42
          },
          Altitude: {
            unit_of_measurement: "m",
            mean: 1700
          },
          Best_of_Class: Date("2023-06-03")
        }
      ]
    }

    The response should include only the document with a mean Altitude value between 1500 and 1800 meters, inclusive.

Delete an index

Delete your index by setting the index name to null in the collection definition:

CoffeeBean.definition.update(
  {
    indexes: {
      bestFlavor: null
    }
  }
)
{
  name: "CoffeeBean",
  coll: Collection,
  ts: Time("2023-06-03T22:07:27.070Z"),
  indexes: {},
  constraints: []
}

The response shows that the bestFlavor index is removed from the collection indexes.

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!