Paginating search results

[eluser]shane_[/eluser]
I wonder what would be a good way to handle pagination of search results. What would be the drawback to dumping the results into a table so that the search query only has to be performed once? Or would it be better to store the search input in a session and perform the search for each page?

[eluser]TheFuzzy0ne[/eluser]
[quote author="shane_" date="1245871514"]I wonder what would be a good way to handle pagination of search results.[/quote]

It really depends on how you've implemented your search.

[quote author="shane_" date="1245871514"]What would be the drawback to dumping the results into a table so that the search query only has to be performed once? [/quote]

You would end up with a humongous table, and some serious performance issues. Especially if any given search is only performed once. Also, that wouldn't account for any new data added to your table.

[quote author="shane_" date="1245871514"]Or would it be better to store the search input in a session and perform the search for each page?[/quote]

I'd say that this was probably one of the best courses of action. Keep it simple. You can always add unnecessary complications at a later date.

[eluser]shane_[/eluser]
I thought about holding the results in the table until the session ended. The app has a limited number of users. But it looks like Google repeats the search query for every page. And I can't seem to find much talk on the Web about do anything else. So I guess that's the way to go. Thanks.

[eluser]lifo101[/eluser]
One search solution I came up with awhile ago for a couple of different projects involves only having to perform the initial search once. Basically, I only perform the query when 'submit' is pressed by the user. I then select only the primary keys and combine them into a CSV string which is saved to a 'search results' table (which is keyed on the users session ID) as a single row.

To page through the results I simply load the CSV string, split it, determine where to 'start' and 'limit' and then perform a simple select on the main table using a where clause that only returns matching rows from the CSV result set.

This provides very fast searches when you have tons of data in the database. It's also a nice way to handle searches when you have to deal with external indexes outside of the database (I use Sphinx indexes for a couple of my projects).

The downside is unless you hard limit the total possible search matches you can run into space and overhead problems in the search results table. Personally, I hard limit matches to about 1000, but that just depends on the application and can be raised/lowered. And for most applications users never go to the end of a search result (I think even google doesn't let you go past a certain point in your search results).

[eluser]shane_[/eluser]
Thanks. That is quite similar to my original thought. This app is not globally accessible. It has a limit, possibly a few thousand users, I haven't really done the numbers. And the searches can be quite complex. It seems terribly inefficient to me to repeat the search on each page request. I believe it will be something I will have to do some testing on to determine which approach works best. Honestly this is my first experience with paginating a search, and wasn't sure what others were doing.