Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
I've got myself into a bit of a predicament ;) A web search that I wrote some time ago works fine in most instances, but my name searches are taking an eternity (as I allow for some flexibility in searching, I'm forced to use an SQL LIKE statement). The results are paginated, so subsequent searches on the same criteria must perform the same search for each page of data displayed. Obviously, this is not terribly efficent, and has become less so with 1.5 million names now in the database. I would like to remedy this.

Here's what I have cooked up currently:

Currently, there are no logins or stateful mechanisms in place. I figure that if I introduce state, I can then build temp tables in the database with the results of certain queries. While the awful name searches will still be awful, they'll only have to be performed once, and the code that displays the results can work off of the temp table.

The session table I have devised looks something like this:
- session_id
- last_updated
- result_temp_table

I figured the following process may work over the course of a typical session:
- User fills in search criteria, hits submit.
- If we have a cookie for this already, make sure the session still exists. If either of these fail, generate a new session ID, cookie, and table entry.
- If the named temp table exists, and the search criteria hasn't changed from the last search, display results from the temp table.
- If the search criteria has changed, drop the temp table (if it exists). Create a new table with a new result set.

Sessions would be deleted every 24 hours or so.

The other part of my problem is with how the search was designed. The first time a search is performed, only a form is shown. When criteria is given and the user presses submit, the criteria are submitted to the same script. . . the first page of results is shown, followed by the search form again (with the criteria filled back in). At the bottom of the result table are links (not buttons) to each of the following pages. They pass certain parameters back to the query search to perform a "fake" submit of sorts. (to get an idea of what I'm talking about, go to, click Begin Search, and do a name search for SMITH JOHN).

In order to make this all work, I need to have some way of determining if the search criteria has changed from the last time the script was run. Seeing as how I already have a fake submit mechanism in place, I figured the easiest way of accomplishing this is to tack an extra parameter on to the page navigation links. The parameter would be absent if the user pressed the submit button, and a new temp table would be created.

My question to you all is then: does this sound reasonable? If not, what have I missed in my design? While it all seems sound to me, I'm still a relative newcomer to web development, and this is the first time I've needed to introduce state into one of my projects. While I normally believe in trying different things until I find the best way of doing it, I'm a bit short on time this time around, and don't think I'll have the time to re-engineer things if I make a critical mistake early on.

As always, your help and insights are appreciated. Thanks in advance,

In reply to Database searches with persistent results via CGI by MrCromeDome

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others chilling in the Monastery: (2)
    As of 2020-02-23 01:29 GMT
    Find Nodes?
      Voting Booth?
      What numbers are you going to focus on primarily in 2020?

      Results (102 votes). Check out past polls.