Advanced index usage
I don't understand why I can't do any of this with a index. I didn't see in the docs a explicit explanation that you couldn't do something like this using the available .eq,gt,gte,lt,lte so i tried a few things.
neq would solve some of this.. but how can just getting all of a index break things too.
Is any of it going to be supported or just via that eventual sql style "adapter"
In one real world example i have a events table with ['lastStatus', 'userid', 'eventType'] where i also have have a index [ 'userid', 'eventType'] when i want all statuses from that user. A gte null on lastStatus index would keep me from duplicating effort.
Probably the most annoying one is to get the last 10 results since a certain time while getting a range of eventTypes for a user ['recordedAt', 'userid', 'eventType']
I fail to see how anyone could ever use more than a few indexes if all but the last one requires .eq usage. I certainly cant imagine complex use cases, I'd love to see some real world work arounds. Or how others here are creating separate tables etc to get more specific.
3 Replies
I'd be happy to dig into how to do any of these queries, since some can be done by defining different indexes and some can be done with multiple queries. But as for why indexes work like this, i recommend https://stack.convex.dev/databases-are-spreadsheets
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...
Basically an index defines a sort order, and an indexed query returns a contiguous set of documents in that sort order. If you want to query a non-contiguous set of documents, you either need a new index or you need to do multiple queries. For example, this is an example of doing multiple queries: https://stack.convex.dev/select-distinct
SELECT DISTINCT without SQL
You'll often want to retrieve unique records from your database. While SELECT DISTINCT is the traditional way to do this in SQL databases, we've impro...
Its all catch 22's, if i run multiple queries then i got to deal with merging data and i cant paginate that right. If i get a larger set then i need and then only return few results i eat through DB bandwidth and need to break tables up so i'm not reading columns of data data i don't need. I probably just need subtypes so i can get particular groups of event types but that is yet another row and a bigger index.
That select distinct example still results in many rows read. I just want to make sure that i know the most "esoteric" way of getting down to a particular result set. But I don't see very many possible combos
our event table is my main concern as its just the single entry point for all mutations and so it will get crazy busy.