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.

Whats 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 catalogue of different movies, where each movie has a unique and random ID and the catalogue is sorted by ID. If you know the movies 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 catalogue to find the one movie. Thats a lot of work! If the catalogue had an index of sorted movie titles and their corresponding ID, on the other hand, youll have a much easier time finding movies.

An index in a database works in a very similar way, only that theyre 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, youll 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 dont 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, youll need to first create them. Ill 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 databse 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 movies 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 databaseconst 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! Thats 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. Its a trade off to get faster reads for slightly 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.

More resources

• Databases Guide
• Appwrite Github
• Appwrite Docs
• Discord Community