Re:For Want of a JOIN
by btilly on Thursday December 22, 2022 @04:21PM
(#34098900)
Huh, I googled for how it worked and found a limit/offset explanation. Then I wandered around the source and verified what you said. I'm not a Rails programmer, so I did get that wrong. (Though I've seen that exact mistake over and over again when people are using microservices. So it is worth being aware of that in your APIs.)
But that said, your "detailed discussion" is going to be wrong for most databases that I've worked with. MySQL makes queries cheap. But PostgreSQL, Oracle, and so on make parsing expensive. Having to parse and try to optimize a good chunk of a MB of SQL is almost certainly more expensive than 50,0000 individual index lookups. (The tradeoff is that the other databases are likely to produce better execution plans if you run the same query over and over again.)
But that said, your "detailed discussion" is going to be wrong for most databases that I've worked with. MySQL makes queries cheap. But PostgreSQL, Oracle, and so on make parsing expensive. Having to parse and try to optimize a good chunk of a MB of SQL is almost certainly more expensive than 50,0000 individual index lookups. (The tradeoff is that the other databases are likely to produce better execution plans if you run the same query over and over again.)