The answer will really depend on the number of rows you anticipate matching and the size of data stored in the rows.
The traditional approach to pagination would be to use SQL LIMIT
clause to return a subset of the overall result set. If you actually needed to know the full number of records (in order to show full number of "pages") then you would need to also make a SELECT COUNT(primary_key_field) FROM table WHERE [search condition]
in order to get that information.
This is generally going to gives best results as the COUNT query should be able to execute very quickly and the LIMIT query will keep query performance responsive so long as you have an index on the field you are using for the sort (as you always want an ORDER BY
along with LIMIT
to guarantee order). This also mean less records you need to keep in memory in your application.
I do suppose there could be some use cases where the single query approach might be better (i.e. not going to have a large number of potential rows, row data size is small), such that you don't have a huge data transfer time of memory usage of retrieving a full result set.
Really, you would have to just test some scenarios in your app and see what works best for you.