[thelist] constructing a good search string/query

[Luther, Ron]

Dan McCullough asked:

>>Wanted a search function that would query my product summary table,
basically 
>>a long string of the colors, description, sizes, and other information
for a 
>>product, and have it return a result.
>>select * from search_string where lower(searchstring) like
'%"+searchTerm+"%'

>>Guess what?  Its SLOWWWWWWWWWWWWWWWWWWWW!
>>Not surprised by that.  


Hi Dan,


Nope, not surprising ... substring searches are generally pretty
'expensive'.

>>So what I am looking for is some advice to speed it up, and possibly
add 
>>in some smarts.

I like offering radio buttons and/or dropdown controls when search
choices 
are fairly limited -- like 'size' or 'color'.  Oftentimes, with a little

thought, you can come up with some 'broad-brush' categories that you can

break out that limit returned results pretty well.

>>however mispelling a name by 1 letter, like leaving off a "n" out of
"Conner" 
>>will cause you to get no results.

Yep.  Drop-downs, (where applicable), make those kinds of problems go
away.

{I do think there are applications, (like a 'white pages' directory for
example), 
where they are not applicable ... because they would contain "too many"
elements 
to be useful.  Use discretion.)

Maybe look at redesigning your search screen to continue to offer
"full/partial 
text" - but as a adjunct to a categorized search.  

You may also want to consider removing or limiting some of these
criteria ... 
a standalone search for everything 'red' or 'medium' isn't really likely
to 
meet an end-user's needs is it?   Don't be afraid to make multiple trips
to the 
server if it improves the 'user experience'.  [e.g. You *could*, as a
basic 
example, "force" the user to select "mens sweaters" or "ladies shoes"
before 
offering a control to allow them to select color.  It doesn't always
have to 
be a single click search.]


HTH,

RonL.