It's quite possible for mysql to materialize temporary tables for the
purpose of evaluating a query. With any if these things you can always
ask the database (on mysql EXPLAIN SELECT * from foos limit 100,200)
what it would do.
If your find clause also has a sort order then you'd better have an
index on that column or it could be dog slow (since then the database
has to sort the entire table to find the top 10 records).