> But they didn't. Instead they made the database parse every single record, compile it, and then try to optimize it. Which will come up with a plan where you had to do index lookup after index lookup. That parsing and optimization overhead is probably most of your time.
See https://news.ycombinator.com/item?id=34095480 for a more detailed discussion of where time is actually spent here, I think this short explanation glosses over a lot of important issues.
> As I understand, it uses limit and offset statements to figure out the result
You are incorrect. Your entire comment is based on a faulty premise. find_each uses an ordered primary ID column which can be queried efficiently using indexes.
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.)
See https://news.ycombinator.com/item?id=34095480 for a more detailed discussion of where time is actually spent here, I think this short explanation glosses over a lot of important issues.
> As I understand, it uses limit and offset statements to figure out the result
You are incorrect. Your entire comment is based on a faulty premise. find_each uses an ordered primary ID column which can be queried efficiently using indexes.