I have a debatable question I would like to pose.
I have a table with 1000 rows of data. I would like to be able to search and view all of these rows in a front end search engine.
I would like to display 50 results per page.
The question is - should I simply return all filtered results (possibly 1000 rows of data) via my repository and have my fluid bootstrap layout handle the pagination OR should I tell my repository to use setLimit and offset to return only the 50 rows of data that should be displayed within the current page?
Originally I figured that either way a SQL statement would be requested on every page call so the latter would be better as the SQL return would be capped at just 50 rows of data, but now I suspect Typo3 utilises some kind of repository or database caching which would mean that the database bulk data is called and stored in memory just once, and the fluid pagination will handle the rest. Plus, using setLimit and Offset I would have to build my own pagination system.
What do you think is more efficient? Should I allow the sql statement to return all the data and have fluid handle the pagination, or should I limit the sql statement with setLimit first and build my own pagination? Or is there a way to mix the two?
Additional Info:
My respository SQL search is fairly straight-forward. It is using:
$query = $this->createQuery();
...plus a "matching" query to filter the results, followed finally by
return $query->execute();
If you use the widget.paginate ViewHelper it will automatically limit the query correctly. The query won't be executed until you try to access the data, so as long as you don't try to access the data outside the paginate ViewHelper there is no need to manually limit it.