Indexes
Indexes are a data structure that allow you to speed up your database queries by telling Convex how to organize your documents. Indexes also allow you to change the order of documents in query results.
If you are new to indexes, start with Introduction to Indexes and Query Performance.
Defining Indexes
Indexes are defined as part of your Convex schema. Each index consists of:
- A name
- Must be unique per table.
- A list of fields to index, in order
- These can either be fields directly on your documents (like
name
) or dot-separated paths referencing fields on nested documents (likeproperties.name
).
- These can either be fields directly on your documents (like
To add an index onto a table use the
index
method on your table's
schema:
import { defineSchema, defineTable, s } from "convex/schema";
// Define a messages table with an index on (channel, _creationTime).
export default defineSchema({
messages: defineTable({
channel: s.id("channels"),
body: s.string(),
user: s.id("users"),
}).index("by_channel_and_creation_time", ["channel", "_creationTime"]),
});
This index is ordered first by the channel
field defined in the schema, and
then by the system-generated _creationTime
field.
Every time you run npx convex push
, the indexes in your deployment will be
updated to match your schema.
You may notice that the first push that defines an index is a bit slower than normal. This is because Convex needs to backfill your index. The more data in your table, the longer it will take Convex to organize it in index order. If this is problematic for your workflow, contact us.
You can feel free to query an index in the same push that defines it. Convex will ensure that the index is backfilled before the new query and mutation functions are registered.
Be careful when removing indexes
In addition to adding new indexes, npx convex push
will delete indexes that
are no longer present in your schema. Make sure that your indexes are completely
unused before removing them from your schema!
Querying Using Indexes
A query for "messages in channel
created 1-2 minutes ago" over the
by_channel_and_creation_time
index would look like:
const messages = await db
.table("messages")
.index("by_channel_and_creation_time")
.range(q =>
q
.eq("channel", channel)
.gt("_creationTime", Date.now() - 2 * 60000)
.lt("_creationTime", Date.now() - 60000)
)
.collect();
The .index
method defines which index to
query. The choice of index both affects how you can filter results in .range
and what order the results are returned in.
The .range
method defines how
Convex will use your index to select documents. An index range is a description
of which documents Convex should consider when running the query.
An index range is always a chained list of:
- 0 or more equality expressions defined with
.eq
. - [Optionally] A lower bound expression defined with
.gt
or.gte
. - [Optionally] An upper bound expression defined with
.lt
or.lte
.
You must step through fields in index order.
Each equality expression must compare a different index field, starting from the beginning and in order. The upper and lower bounds must follow the equality expressions and compare the next field.
For example, it is not possible to write a query like:
// DOES NOT COMPILE!
const messages = await db
.table("messages")
.index("by_channel_and_creation_time")
.range(q =>
q
.gt("_creationTime", Date.now() - 2 * 60000)
.lt("_creationTime", Date.now() - 60000)
)
.collect();
This query is invalid because the by_channel_and_creation_time
index is
ordered by (channel, _creationTime)
and this query range has a comparison on
_creationTime
without first restricting the range to a single channel
.
Because the index is sorted first by channel
and then by _creationTime
, it
isn't a useful index for finding messages in all channels created 1-2 minutes
ago. The TypeScript types within range
will guide you through this.
To better understand what queries can be run over which indexes, see Introduction to Indexes and Query Performance.
The performance of your query is based on the specificity of the range.
For example, if the query is
const messages = await db
.table("messages")
.index("by_channel_and_creation_time")
.range(q =>
q
.eq("channel", channel)
.gt("_creationTime", Date.now() - 2 * 60000)
.lt("_creationTime", Date.now() - 60000)
)
.collect();
then query's performance would be based on the number of messages in channel
created 1-2 minutes ago.
Picking a good index range
For performance, define index ranges that are as specific as possible! If you
are querying a large table and you're unable to add any equality conditions with
.eq
, you should consider defining a new index.
.range
is designed to only allow you to specify ranges that Convex can
efficiently use your index to find. For all other filtering you can use the
.filter
method.
For example to query for "messages in channel
created by me" you could do:
const messages = await db
.table("messages")
.index("by_channel_and_creation_time")
.range(q => q.eq("channel", channel))
.filter(q => q.eq(q.field("user"), myUserId))
.collect();
In this case the performance of this query will be based on how many messages
are in the channel. Convex will consider each message in the channel and only
return the messages where the user
field matches myUserId
.
Limits
Convex supports indexes containing up to 16 fields. You can define 32 indexes on each table. Indexes can't contain duplicate fields.
The _id
field is automatically added to the end of every index to ensure a
stable ordering. Because indexes can't include duplicate fields, you cannot
include _id
as a field in your indexes.
The by_id
and by_creation_time
indexes are reserved.