Taming Slow Appwrite Queries: A Practical Guide to Database Indexes

Your Appwrite collection started small. A few hundred documents, and every list request returned in milliseconds. Then the collection grew past a few thousand records, and the same query began to stall. The application code did not change, but the database now evaluates your filter against every single document. The useful takeaway is straightforward: if you filter or sort on an attribute, that attribute usually needs an index.

Why unindexed queries slow down

When you call listDocuments with a Query.equal or Query.orderDesc on an attribute that has no index, Appwrite has to inspect each document to decide whether it matches or where it belongs in the sort order. That is a collection scan. As the collection grows, latency grows with it. An index is an auxiliary lookup structure that maps attribute values to their documents, so the database can jump directly to the relevant entries instead of walking the whole set.

Adding an index in Appwrite

Appwrite supports explicit indexes on collection attributes. An index is defined by one or more attribute keys and a type—commonly key, fulltext, or unique. You can manage them per collection in the Appwrite Console, or create them programmatically through a server SDK. Because Appwrite has evolved its database API (the shift from Collections to Tables), confirm the exact resource and method names for your deployed version before writing automation.

Using the Console

1. Open the Appwrite Console and navigate to your project.
2. Select Databases, then the database and collection you want to optimize.
3. Open the Indexes tab to inspect what already exists.
4. Create a new index, choose the type, and select the attribute keys you filter or sort on.
5. Save. On very large existing collections, index creation may take time to become effective.

Using a server SDK

If you prefer automation, the server SDKs expose an index-creation method (often named createIndex, though signatures differ across releases). Run it from your backend with a project API key that has write access to the target database. Check the SDK reference for your language to confirm parameter order and required scopes.

Worked example: filtering and sorting posts

Suppose you have a posts collection with a status string and a publishedAt datetime. Your app lists published posts newest first with server-side pagination:

Query.equal('status', 'published'),
Query.orderDesc('publishedAt'),
Query.limit(25)

If neither status nor publishedAt is indexed, Appwrite scans the collection to apply the filter and then sorts the result. Add a key index that covers both attributes so the index supports the equality filter and the descending sort together. After the index is active, the same query should target the matching range directly. Keep the limit and cursor pagination in place; an indexed sort attribute pairs naturally with cursor-based pagination to keep large result sets responsive.

Trade-offs and how to check the result

Indexes are not free. Every added index increases write cost and storage usage, so avoid indexing attributes that are rarely queried. Fulltext indexes tokenize and normalize text differently than exact-match key indexes; validate expected search behavior with real data before relying on them.

Index type	Best for	Watch out for
key	Equality filters, range queries, sorting	Not suitable for fuzzy text search
fulltext	Searching inside text attributes	Tokenization differs from exact match
unique	Enforcing one-of-a-kind values	Adds a write-time uniqueness check

To verify the change, measure query latency before and after adding the index. You can compare response times directly in the Console or from your application using the same query parameters. If the index does not improve performance, remove it—unused indexes only add write overhead.

Actionable next step

Audit your slowest queries today. Open the Appwrite Console, look at each hot collection's Indexes tab, and compare the attributes you filter or sort on against the indexes that exist. If a hot attribute is missing, add a key index during a low-traffic window, re-run the query, and confirm the response time improved.