How to avoid hitting the limit while joining and sorting two tables
I have two tables:
- documentAccess: userId, documentId, ... (revokedAt - not important for the question)
- documents: id, isActive (filterable property), sortableFiled1, sortableField2 etc
The goal: get the first X (10/20/50/100) documents, sorted by one of the sortable fields, that belong to the user (can be viewed by the user)
I have looked both into streams and convex regular syntax, but in both cases I can see scenarios where I might hit the max doc(rows) scan: over 36.000 or so
In both cases imagine having more than 100k documents.
If I start with the documents sorted, the user might have no documents or very few, resulting in hitting the limit
If I start with the access, I'll need to scan all the documents the user has access to (and there might be more than 36.000, or half of that considering I have to query then the documents table), so I'll be able to sort/filter them, resulting in the same effect, hitting the limit.
1 Reply
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!