Take the 2-minute tour ×

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 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.

share|improve this question

3 Answers 3

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.

share|improve this answer
The result set could be couple thousand rows and would include images, so I am not sure how that will work with DataTables. I will take a look at everything you have suggested. Thanks –  rpat Feb 23 '11 at 14:15

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.

share|improve this answer
Try EXPLAIN SELECT... and see what's taking so long. Make sure the appropriate columns are indexed. –  Paul Schreiber Feb 21 '11 at 15:37
There are 2 parts to improving this. First is to improve the query itself. I have had a look at this and still looking at improving this. The second part is really where I needed the clarification. Should I be doing a new query every time or should I cache the results. If I am able to cache the results, then it would save me a bunch of queries altogether. –  rpat Feb 23 '11 at 14:18

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

share|improve this answer
Let me look at it. Thanks –  rpat Feb 23 '11 at 14:19

Your Answer


