djbalin•2w ago

[Compound indexes]: Query first field, then sort by _creationTime

How can I prune redundant indexes as per the Convex best practices but still leverage that _creationTime is automatically added as the final index field to all indexes ? Wouldn't it be possible to implement something like a q.all that just aggregates/ignores a particular index field? For example:

// index definition
const schema = defineSchema(
  {
    video: defineTable(video).index("by_channelId_privacyStatus", ["channelId", "privacyStatus"])
  }
)

// query all videos from a given channel within a time range
...
    const videos = await ctx.db
      .query("video")
      .withIndex("by_channelId_privacyStatus_reviewStage", (q) =>
        q
          .eq("channelId", channelId)
          .all("privacyStatus")
          .gte("_creationTime",oneMonthAgo),
      )
      .order("desc")
      .collect();
...

// index definition
const schema = defineSchema(
  {
    video: defineTable(video).index("by_channelId_privacyStatus", ["channelId", "privacyStatus"])
  }
)

// query all videos from a given channel within a time range
...
    const videos = await ctx.db
      .query("video")
      .withIndex("by_channelId_privacyStatus_reviewStage", (q) =>
        q
          .eq("channelId", channelId)
          .all("privacyStatus")
          .gte("_creationTime",oneMonthAgo),
      )
      .order("desc")
      .collect();
...

Maybe this would be less efficient since we would need to re-sort all documents after aggregating over one of the further nested index fields?

Indexes | Convex Developer Hub

Indexes are a data structure that allow you to speed up your

Best Practices | Convex Developer Hub

This is a list of best practices and common anti-patterns around using Convex.

2 Replies

Convex Bot•2w ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

sshader•2w ago

Yeah if you care about sorting / filtering on _creationTime and are loading enough documents that re-sorting in JS is expensive, then you probably want two separate indexes (this is in the "Exceptions" section of the best practices doc). It's ultimately a trade off of write overhead / more storage (for more indexes) vs. query performance -- something like .all("privacyStatus") could be implemented as N separate queries, but if restricting to _creationTime to one month ago is the more important constraint, sounds like you might need an index on channelId, _creationTime in addition to channelId, privacyStatus https://stack.convex.dev/databases-are-spreadsheets is how I visualize what can and can't be done efficiently with an index (e.g. why all would have to be N separate queries)

Databases are Spreadsheets

I want to share my mental model of databases: - Databases are just big spreadsheets - An index is just a view of the spreadsheet sorted by one or mor...

[Compound indexes]: Query first field, then sort by _creationTime

Did you find this page helpful?