The Appwrite Hackathon on DEV has just concluded and one of the frequent pain points among new Appwrite developers is with adding indexes to collection. When attempting to query a collection or sort the documents returned, Appwrite developers frequently faced errors related to missing or incorrect indexes. This post will cover what indexes are, why we need them, and how to use them correctly in Appwrite.
What’s an Index?
An index helps you search a collection of documents more efficiently. Appwrite requires an index for all sort and query type operations to ensure your application runs efficiently.
Imagine a collection as a catalog of different movies, where each movie has a unique and random ID and the catalog is sorted by ID. If you know the movie’s ID, finding the movie is easy. If you wanted to look up a film by a particular title, you might have to look through the entire catalog to find the one movie. That’s a lot of work! If the catalog had an index of sorted movie titles and their corresponding ID, on the other hand, you’ll have a much easier time finding movies.
An index in a database works in a very similar way, only that they’re implemented using data structures such as B-Trees. The performance of an index lookup is O(log(n)), while a full table scan is of O(n). That's a huge difference!
Indexes found in Appwrite rely on the implementation found in the underlying database. As of today, Appwrite only supports MariaDB as a database adaptor, so the behavior of indexes and querying indexes should be very similar to that of MariaDB.
When do I need an index?
For performance reasons, Appwrite requires an index for every query and sort operation. If you need to query multiple attributes in the same query, you’ll need an index with multiple attributes. Appwrite supports three types of indexes.
- Key: Plain indexes that allow you to perform sorts and queries.
- Unique: Indexes that don’t allow duplicates, useful for indexing unique identifiers.
- Fulltext: Indexes that allow you to query string attributes that may not match the query parameter exactly. You can use this field type to look for keywords in movie descriptions, for example.
When an index is created, an equivalent index is created in the underlying database (MariaDB) to make efficient queries and sorting possible.
Using Indexes in Appwrite
Before you can consume an index, you’ll need to first create them. I’ll be using a collection of Disney movies as example to show you how to create different types of indexes and how to query them. The collection configuration and code referenced can be found in this Gist
The collection belongs to a database called entertainment has the following attributes:
We will create indexes to allow querying for movies by title, querying unique movies by title and year, as well as searching movie descriptions using keywords.
Creating Indexes
To search for movies by title, we need to create an index with the name title and type Key. This allow us to query for all movies that share the same title. The match is exact.
To find unique movies and filter out remakes or duplicate titles, we can create a Unique index named title-and-year. Notice how this index includes both title and year attributes. This is necessary to query both attributes at the same time.
To search for keywords in a movie’s description, we can create a Fulltext index named description.
Querying Using Indexes
To find every version of “The Lion King”, the query will look like this:
// Point sdk to entertainment database
const databases = new sdk.Databases(client, 'entertainment');
// Query for the movie "The Lion King"
databases.listDocuments('movies', [sdk.Query.equal('title', 'The Lion King')]),
This will returned the following:
{
"total":2,
"documents":[
{
"year":2019,
"title":"The Lion King",
"description":"The animated remake of Lion King",
"$id":"62a793e62d7c105ee92f",
"$read":[
],
"$write":[
],
"$collection":"movies"
},
{
"year":1994,
"title":"The Lion King",
"description":"The original cartoon Lion King from Disney.",
"$id":"62a793e631cce5281683",
"$read":[
],
"$write":[
],
"$collection":"movies"
}
]
}
To find only the 1950 edition of Cinderella, the query will look like this:
databases.listDocuments('movies', [sdk.Query.equal('title', 'Cinderella'), sdk.Query.equal('year', 1950)]),
This will returned the following:
""{
"total":1,
"documents":[
{
"year":1950,
"title":"Cinderella",
"description":"With a wicked stepmother (Wilfred Jackson) and two jealous stepsisters (Homer Brightman, Harry Reeves) who keep her enslaved and in rags, Cinderella (Clyde Geronimi) stands no chance of attending the royal ball.",
"$id":"62a793e6886c41162589",
"$read":[
],
"$write":[
],
"$collection":"movies"
}
]
}
Finally, to search for all remakes, we can search descriptions for the keyword remake:
databases.listDocuments('movies', [sdk.Query.search('description', 'remake')])
This yields the following results:
{
"total":2,
"documents":[
{
"year":2019,
"title":"The Lion King",
"description":"The animated remake of Lion King",
"$id":"62a793e62d7c105ee92f""$read":[
],
"$write":[
],
"$collection":"movies"
},
{
"year":2015,
"title":"Cinderella",
"description":"The 2015 remake of the original animated Cinderella.",
"$id":"62a793e688f4ff05f620",
"$read":[
],
"$write":[
],
"$collection":"movies"
}
]
}
Et voilà! That’s how you create and query indexes with Appwrite!
Final Remarks
Indexes come at a cost. They require additional data structures which costs processing to create and maintain on writes, as well as more storage space. It’s a trade off to get faster reads for slightly slower writes speeds and more storage space. As a developer, you should be conscious of which attributes you need to query and only create necessary indexes.