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