'too much memory' is a relative thing.
If your result rows are 2 integers, it's gonna take a -lot- of rows to generate too much memory.
If your result rows are full text pages, it's gonna take a lot fewer.
Generally speaking, most queries should be fine up to a couple thousand results.
That said; how to handle the need to display huge numbers of rows.
In what context would you need to display huge numbers of rows that couldnt benefit from a bit of Pagination?