[thelist] constructing a good search string/query

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Roger Ly
: Subject: RE: [thelist] constructing a good search string/query
: 
: >Here is the query.
: >select * from search_string where lower(searchstring) like
: >'%"+searchTerm+"%'
: 
: How the does the query compare when run without 
: the lower function?  Is it faster?  You could 
: consider creating a functional index on search_string
: for lower(searchstring) to help optimize the query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

It would really help if we knew what DBMS Dan was using.

An index on this field in SQL Server is not going to help, since a search
that has a wildcard at the front of the search term will result in a full
table scan. If the wildcard is at the end (or the middle), then the index can
be used (which makes sense when you think about it, since a wild card at the
beginning of the field can, potentially, match any starting character,
whereas a wildcard in the middle, or end, implies a limit all already on the
records that can be returned).

If this was SQL Server, I would look at Full Text Search. And making sure
your table statistics are up to date.

Additionally, as has been pointed out, Lower() is unnecessary unless you have
case sensitivity set for your DBMS.

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/