Implementing MySQL search results pagination

This ought to be a fairly common problem. I could not find a solution in the stackoverflow questions database. I am not sure I did the right search.

I run a MySQL, CGI/Perl site. May be a 1000 hits a day. A user can开发者_JAVA技巧 search the database for the website. The where clause can become quite lengthy. I display 10 items per page and give the user links to go to the next and previous pages. Currently, I do a new search, every time the user clicks on 'prev' or 'next' page link. I use

LIMIT num-rows-to-fetch OFFSET num-rows-to-skip

along with the query statement. However, response time is way too much for subsequent searches. This can only get worse as I add more users. I am trying to see if I can implement this in a better way.

If you can give me some pointers, I would appreciate it very much.

If you don't mind using Javascript, you should check DataTables. This way you send all rows to the client, and pagination is done on client side.

If it is not an option, then you could try using mod_perl or CGI::Session in order to save the query result between page querys, so you will not need to query mysql again and again.

you could try analyzing the query to find out which part causes the most trouble for the database. If there are joins, you could create indexes that include both the key fields and possible filter fields used in the query. But that could, depending on the database layout and size, create a lot of indexes. And if the query can change significantly you may still be at a loss if you did not create an index for a particular case. In any case i would at first analyze the query, trying to find out what makes the query so slow. If there is one major cause for the bad performance you could try throwing indexes at it.

Another way would be to cache the search results in memory and do pagination from there, avoiding the database roundtrip at all. Depending on your database size you might want to limit the search results to a reasonable number.

I have never used this module, so cannot vouch for it's performance, but have a look at DBI::ResultPager