Re: Re: Paging through records on the web - Mailing list pgsql-general

Alternatively, you could do your search once, store the results in a table
along with a search id number, and then just SELECT ... FROM search_cache
WHERE searchId = $SearchID LIMIT $X OFFSET $Y  That would cut down on the
processing time of a  complex search (ie, searching a product database
based on multiple text fields)

I've been meaning to do this for a search system we run here, but haven't
gotten around to it.  I'm looking for a guinea pig to see if it's worth the
effort. :)  Alternatively, instead of storing the whole result tuple, you
could just store the OID's and do a SELECT fields FROM search_table WHERE
oid IN (SELECT sved_oid FROM search_cache WHERE searchId = $SearchID LIMIT
$X OFFSET $Y)  Easier to implement, and probably not too much slower.

Any PostGres gurus out there who could hazard a guess as to what sort of
speed increase you'd get by searching based on one int (like the OID or
search_id) in comparison to one or two text/varchar fields?

At 03:02 PM 6/8/00, K Parker wrote:
> >> Where in postgresql you would say
> >>
> >>       select * from foo limit 20
>...
> >
> > Related question: A typical web search interface
> > needs a page based browsing system where you
> > can list the 10 next matches...
> > I'm thinking of something like :
> >
> > select * from foo where <some search criteria> and
> >        rownum >= 30 and rownum < 40
>
> > Or is this where I should look into using
> > cursors to access the result set ?
>
>I don't think cursors will work without some
>very fancy back-end programming to match up each successive web-page
>request with
>_the same_ process so you have somewhere
>to maintain that cursor.
>
>As an alternative, you're almost certainly presenting the records
>in sorted order, so you may be able find a unique or almost-unique
>set of fields to control the starting record.
>
>The following is from a PHP application that displays a user's login
>history in reverse order.  '$_init_date' is sent via a hidden variable
>when the user presses the MORE submit button at the end of each page:
>
>    $max_time_rows = 20;
>    if ( $_init_date == '' )
>       {
>       $qry = "select checktime, status from checkin \
>          where acct = $_acct \
>          order by checktime desc limit $max_time_rows";
>       }
>    else
>       {
>       $qry = "select checktime, status, from checkin \
>          where acct = $_acct and checktime <= '$_init_date' \
>          order by checktime desc limit $max_time_rows";
>       }
>
>Sure, it's theoretically possible that there will be 2 or more
>rows with the exact same login or logout time, but it's unlikely,
>and the only harm that results is carrying forward the bottom
>row or two onto the next page.
>
>
>
>Join 18 million Eudora users by signing up for a free Eudora Web-Mail
>account at http://www.eudoramail.com