[thelist] search engine issue (development issue that is)

[Chris Blessing]

Colin-

Some excellent points, thank you! I really like the idea of saving search
terms.  That way my search results table can act as a growing/shrinking
cache of searches, resulting in much less pain and agony for the db server.
These searches are NOT the quickest either, so it'd be nice to have that
performance boost.

Basically I could alter my plan to work like this:

1) user enters search terms
2) search terms are looked up in the previously searched terms table in the
db
2a) if the search terms are not found, search terms are saved in a table w/
the results (something like searchTerms.id = searchResults.id so we don't
have repeating search terms AND we have a smaller table to search the actual
search terms in step #2)
2b) if the search terms are found, display the previously-generated results
3) output results page 20 at a time (easy since we have all the results in
the search results table)

I like it!

Now if a user is on the results page and they request the next 20 results
(for example), I can identify their search purely on the search terms, not
necessarily on the sid or the search id, since it's totally irrelevant.

Next I setup a little window-less vb app to run every morning at 4am
(minimizing the possibility of partial search results being returned if the
delete/select take place at the same time) via Task Scheduler.

NICE.  Am I blind or is this damn near perfect?

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> Hmmm... this is an interesting question.  I can think of a few changes I
> might consider, both to make things simpler, and (possibly) a little
> quicker:
>
> 1 - I would consider basing your temp table not on a user's
> session_id, but
> on the search terms themselves.  This way, if multiple users are searching
> for the same keywords (which may or may not be the case for you, but it's
> certainly something I've seen), then they can rely on the same entries in
> the temp table.  You save space, and you aren't repeating DB queries when
> you don't need to.
> 2 - In terms of cleaning out the table, I would take an approach
> like this -
> put a timestamp into the table, and run a process every now and then to
> clean out old searches.  This leads me to idea #3...
> 3 - If a user comes back to the website and tries to do a search for which
> the data has been cleaned out of your table, just regenerate the
> entries and
> go from there.  This is something that you're probably going to need to be
> able to do that anyway in case a user bookmarks a search result page, etc,
> etc.  Are you passing in your search terms on the URL?
>
> Hope that helps - Colin