Filter and sort with indexes

This tutorial shows how you can fetch, filter, and sort data using indexes.

Database indexes are the most important and effective tool you can use to increase the performance and reduce the cost of your queries.

Indexes let you search and sort a collection’s documents in a performant way. We highly recommend you create indexes for your all of your common data access patterns. Unindexed queries should be avoided.

Define an index

You define indexes in Fauna Schema Language (FSL) as part of a collection schema. You manage schemas using the Fauna Dashboard or the Fauna CLI.

An index definition can include:

  • Terms: Document fields for exact match searches

  • Values: Document fields for sorting and range searches

collection Product {
  ...

  index byName {
    terms [.name]
    values [desc(.quantity)]
  }

  ...
}

An index definition must include at least one term or value. All indexes implicitly include an ascending document id as the index’s last value.

The index stores, or covers, these fields for each document in the collection. When called, the index quickly fetches the values without scanning each document.

Sort documents

Fauna’s demo data includes the sortedByPriceLowToHigh index in the Product collection’s schema:

collection Product {
  ...

  index sortedByPriceLowToHigh {
    values [.price, .name, .description]
  }
}

In an FQL query, you call an index as a method on its collection.

Call the sortedByPriceLowToHigh index with no arguments to return Product documents sorted by:

  • Ascending price, then …​

  • Ascending name, then …​

  • Ascending description, then …​

  • Ascending id

// Get products by ascending price, name, and description
Product.sortedByPriceLowToHigh()

Calling an index returns a Set of matching documents:

{
  data: [
    {
      id: "394086673314480192",
      coll: Product,
      ...
      name: "limes",
      description: "Conventional, 1 ct",
      price: 0.35,
      quantity: 995,
      ...
    },
    ...
    {
      id: "394086673309237312",
      coll: Product,
      ...
      name: "pinata",
      description: "Original Classic Donkey Pinata",
      price: 24.99,
      quantity: 40,
      ...
    }
  ]
}

Descending order

By default, index values sort in ascending order. To use descending order, use desc(<field>) in the index definition.

The following index definition sorts products by descending price, followed by ascending name and description:

collection Product {
  ...

  index sortedByPriceHighToLow {
    values [desc(.price), .name, .description]
  }

  ...
}

Call the index with no arguments to return Product documents sorted by:

  • Descending price, then …​

  • Ascending name, then …​

  • Ascending description, then …​

  • Ascending id

// Get products by descending price,
// ascending name, and ascending description
Product.sortedByPriceHighToLow()

You can also use index values for range searches.

The sortedByPriceLowToHigh index specifies price as its first value. The following query passes an argument to run a range search on price:

// Get products with a price between
// 20 (inclusive) and 30 (inclusive)
Product.sortedByPriceLowToHigh({ from: 20, to: 30 })

If an index value uses descending order, pass the higher value in from:

// Get products with a price between
// 20 (inclusive)and 30 (inclusive) in desc order
Product.sortedByPriceHighToLow({ from: 30, to: 20 })

Omit from or to to run unbounded range searches:

// Get products with a price greater than or equal to 20
Product.sortedByPriceLowToHigh({ from: 20 })

// Get products with a price less than or equal to 30
// Product.sortedByPriceLowToHigh({ to: 30 })

Pass multiple index values

Use an array to pass multiple value arguments. Pass the arguments in the same field order used in the index definition.

Product.sortedByPriceLowToHigh({ from: [ 20, "l" ], to: [ 30, "z" ] })

The index returns any document that matches the first value in the from and to arrays. If matching documents have the same values, they are compared against the next array element value, and so on.

For example, the Product collection’s sortedByPriceLowToHigh index covers the price and name fields as index values. The Product collection contains two documents:

Document

price

name

Doc1

4.99

pizza

Doc2

6.98

cups

The following query returns both Doc1 and Doc2, in addition to other matching documents:

Product.sortedByPriceLowToHigh({ from: [4.99, "p"] })

The first value (4.99 and 6.98) of each document matches the first value (4.99) of the from array.

Later, you update the document values to:

Document price name

Doc1

4.99

pizza

Doc2

4.99

cups

The following query no longer returns Doc2:

Product.sortedByPriceLowToHigh({ from: [4.99, "p"] })

Although the first value (4.99) in both documents matches the first value in the from array, the second value (cups) in Doc2 doesn’t match the second value (p) of the from array.

Run a range query on id

All indexes implicitly include an ascending document id as the index’s last value.

If you intend to run range queries on id, we recommend you explicitly include an ascending id as the last index value in the index definition, even if you have an otherwise identical index.

For example, the following sortByQuantity and sortByQuantityandId indexes have the same values:

collection Product {
  ...

  index sortByQuantity {
    values [.quantity]
  }

  index sortByQuantityandId {
    values [.quantity, .id]
  }

  ...
}

Although it’s not explicitly listed, sortByQuantity implicitly includes an ascending id as its last value.

To reduce your costs, Fauna only builds the sortByQuantity index. When a query calls the sortByQuantityandId index, Fauna uses the sortByQuantity index behind the scenes. sortByQuantityandId only acts as virtual index and isn’t materialized.

Use index terms to run exact match searches on a field.

The following index definition includes name as an index term:

collection Product {
  ...

  index byName {
    terms [.name]
    values [desc(.quantity)]
  }

  ...
}

When you call the index, you must pass an argument for each term in the index definition.

// Get products named "limes"
Product.byName("limes")

Pass multiple index terms

The following index definition includes two index terms:

collection Customer {
  ...

  index byName {
    terms [.firstName, .lastName]
  }
}

In an index call, use a comma to separate multiple term arguments. Provide arguments in the same field order used in the index definition.

// Get customers named "Alice Appleseed"
Customer.byName("Alice", "Appleseed")

Combine terms and values

If an index has both terms and values, you can run an exact match search on documents in a provided range.

// Get products named "pinata"
// with a quantity between 10 (inclusive) and 50 (inclusive)
Product.byName("pinata", { from: 50, to: 10 })

Covered queries

If you project an index’s covered term or value fields, Fauna gets the field values from the index. This is a covered query.

// This is a covered query.
// `name`, `description`, and `prices` are values
// in the `sortedByPriceLowToHigh` index definition.
Product.sortedByPriceLowToHigh() {
  name,
  description,
  price
}

If the projection contains an uncovered field, Fauna must retrieve the field values from the documents. This is an uncovered query.

// This is an uncovered query.
// `quantity` is not one of the terms or values
// in the `sortedByPriceLowToHigh` index definition.
Product.sortedByPriceLowToHigh() {
  quantity,
  name
}

Covered queries are typically faster and less expensive than uncovered queries, which require document reads. If you frequently run uncovered queries, consider adding the uncovered fields to the index definition.

View indexes for a collection

You can view index definitions as part of a collection schema in the Dashboard.

dashboard collection schema

If you manage schemas using the Fauna CLI, you can view index definitions in the source .fsl file for your collection schema.

You can also fetch a collection schema using <collection>.definition in an FQL query. For example:

Product.definition

The response includes any index definitions as objects in the indexes property. The following collection schema includes the byName index definition:

{
  name: "Product",
  coll: Collection,
  ...
  indexes: {
    byName: {
      terms: [
        {
          field: ".name",
          mva: false
        }
      ],
      values: [
        {
          field: ".quantity",
          order: "desc",
          mva: false
        }
      ],
      queryable: true,
      status: "complete"
    },
    ...
  },
  ...
}

Delete an index

To delete an index, remove its definition from the collection schema using the Fauna Dashboard or Fauna CLI. With the CLI:

  1. Edit the .fsl file for the collection schema and remove the index definition.

  2. Run fauna schema push to update the schema in Fauna.

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!