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.
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 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. But even ignoring that, a single scan for `n` things in an index with `m` things winds up taking an average time `O(n log(m/n))`. Which is generally faster than the `O(n log(m))` of separate lookups.
This change saves a tremendous amount of work on the database, and therefore reduces contention for resources. That's database 101, and any competent DBA should be able to give you the lecture. As a programmer you might not understand how much of a difference it makes. But trust me, it does.
Now about data quantity. You're giving cargo cult advice on queries that is only sometimes going to be right. What is the actual tradeoff for find_each?
The one win is that you return limited data on each trip. 50,000 records really isn't that much these days, so I discount the win. But it can matter, particularly for memory constrained containers.
But what is happening inside the database if you fetch 50,000 records from a join, in batches of a thousand? As I understand, it uses limit and offset statements to figure out the result. But how doe that work?
First, it calculates the join to find 1000 records and returns them.
Second, it calculates the join to find 2000 records, throws 1000 away, and returns the rest.
Third, it calculates the join to find 3000 records, throws 2000 away, and returns the rest.
And so on until it has found a full 1,275,000 records, of which it has thrown away 1,225,000 and returned 50,000. Guess what this means for total database work required? And the behavior is fundamentally quadratic. If you have 10x the data to process, your database has to do 100x the work.
There are definitely a lot of use cases where you need to batch records. But your batch size should be as large as you can comfortably use. And you need to realize that you're trading off trading up front memory for time and more work inside of the database.
The next time that you find yourself having to go down the "optimization tree", I strongly recommend considering whether you're in fact trying to put a patch on a self-inflicted wound. Try proper joins, indexes, and a larger batch size first. See how much of a difference that makes.
Alternately take advantage of the fact that you know your tables in a way that Rails doesn't. Order the results by primary key. Every time you fetch a batch, record the largest primary key you returned. Then instead of offset/limit on the next query, use a limit and a condition on the key. This will eliminate almost all of the duplicate work in most situations, at the cost of having somewhat more fragile logic.