[Israel.pm] Next/Previous item from database

Gabor Szabo gabor at perl.org.il
Tue May 25 18:15:48 PDT 2004

I have an SQL database with a few words in it. It has a web
interface to search among the words. Once I found a subset of
words it displays them all and I can select one of them.
>From that point I'd like to be able to go to the Next or the
Previous word within my subset. Asking the user to click on
the next or the previous radio button in the displayed list
is not in the solution set.

Theoretically the subset can be the whole list as well. There
will be about 30-50,000 words in my database.

How can I do this easily and efficiently ?

I thought of

1) On the Next or Prev button I'll send back the search, and the
current word, retrieve the full list into memory in an array sorted,
find the current word (going through all the elements ?) and then
retrieve the Next or Previous element.

2) On the original search I build an ordered hash from the results,
save it in my Session object (currently saved in file). When the
Next or Previous button is pressed I only supply the current value
and fetch the Next and Previous value from the saved data.

3) I can mix the two and build the ordered hash on every request but
I guess it will just slow things down further.

4) I could create a result table in the database on each search and
then use that table for the subsequent requests within my subset.

The problem with 1-3 approaches seem to be that when the result set is
large (10,000 words) then the whole thing seems to be quite slow.
Maybe this is because I am using Class::DBI or maybe because SQLite ?

4 seem to be too much work :)

Any other ideas how to solve this thing ?


More information about the Perl mailing list