Mailing List Archive


Back to the month index Back to the list index
John Hascall (john@iastate.edu)
Thu, 05 Sep 1996 13:43:36 CDT 

Message-Id: <9609051843.AA02851@pvmon.cc.iastate.edu>
Subject: Re: [mSQL] Should I create tables for intermediate results? 
Date: Thu, 05 Sep 1996 13:43:36 CDT
From: John Hascall <john@iastate.edu>

> I'm using php & msql for an online bookstore. I need to support multi-page
> results of searches, where each page might display the "next 10" books.

> I've got several options. One option I've considered is to create a
> temporary table with the results of the search and store the data in it
> (along with a numbering field), then retrieve at will over the next few
> pages, possibly using cookies. On the msql end, is this a good or bad idea
> w.r.t. performance? I suppose this could lead eventually to caching
> searches, to optimize performance... I've got lotsa disk space ... hmm.
>
> Any experience or opinions will be helpful. I'll summarize if you send to me

  I am using mSQL and C (none of that weenie php stuff for me :)
  to do a similar thing (see http://www.augusthome.com/database/search.html)
  [this is a for-hire, so I can't give out the code, but I can share my
  ideas], anyway, I do roughly:

       if (cached(search-params)) {
            get row-count from cache
       } else {
            do mSQL lookup
            get row-count
       }
       output page start, and '<ul>'
       for (i = 0; i < row-count; ++i) {
            if (cached) {
                read-a-row-from-cache
            } else {
                get-a-row-from-msql
            }
            format row as an <li> line
            if (!cached) save row in cache
            if (display-this-row(i)) /* e.g., responses 21-40 */
                output row
            }
       }
       output '</ul>' and end of page

    Performance is pretty good, the cache makes getting
    subsequent pages a lot faster than doing the query
    over and over again (and if multiple people do the
    same query, you get even more of a win).

John
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com. Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!