If I have a product search and it returns say 200 products. The results are displayed 12 per page. Is it better to:
simply run the search for every result page?
save the search results (IDs of products) in a session array and get them from a database one-by-one via their primary key (ID) 12 times per page?
The second option seems best to me but maybe MySQL has some optimization / cache that would make this unnecessary? Also what if there are 36 or 72 results per page, would running the search one time and retrieving 36-72 products by their primary key be faster? It’s a pain in the ass to measure because the database is not yet populated.
Ok but when you specify an offset and limit you’re still running the search, only starting from another place and stopping after a set number of results.
What I wanted to know is if it’s better to run the search once, store the IDs of results (products) in an array session variable and work with that or just run the search multiple times.
my advice: try it both ways and see which one you like
the choice might also depend on how expensive the search query is
the expense has two factors: how many rows must be accessed, and how many rows are actually returned
a query involving numerous complex joins on many large tables only to return a relatively few rows is a different scenario from a straightforward query that zips through a simple table and returns most of the rows
