2 replies

[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

_creationTime

_creationTime

_creationTime is automatically added as the final index field to all indexes ?

Wouldn't it be possible to implement something like a

q.all

q.all

q.all

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();
...

// 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.

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

Similar Threads

Similar Threads

Similar Threads