3 ms·
How do you join data across hundreds of databases? And how is the query plan for indexes when doing so?
by hu3 5y ago
How do you join data across hundreds of databases?
And how is the query plan for indexes when doing so?
- kevingadd 5y agoMy understanding is that it just works. They're still all in the same process, and SQLite has all the information it needs to do joins. Transactions get more complex but are still atomic.
- daliusd 5y agoYou don’t.
- samatman 5y agoUsing ATTACH. Works a treat. SQLite query planner knows about ATTACH.
- rogers18445 5y agoI have seen it suggested to use one table per db to minimize lock contention and simplify backups/vacuums. Do you know if there really is nothing to lose with ATTACH spam? Any tradeoffs at all?
- samatman 5y agoI wouldn't design that way. SQLite has a default limit of 10 attached databases and a hard limit of 125. Having several areas of concern managed by distinct databases, with some occasions which warrant joins across the domains, this is the more natural use pattern. ATTACH loses foreign key constraints and triggers, among other things.