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 http://www.stclaircountyrecorder.com, 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,
MrCromeDome

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.