[thelist] constructing a good search string/query

[Dan McCullough]

Its MySQL 4, and I went back to not using the wildcard on the select statement.

On 5/3/05, Ken Schaefer <Ken at adopenstatic.com> wrote:
> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : 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/
> --
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>