RE: fast search or scan for words in selected field

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

On LIKE keyword, index cannot be used if wildcard is used at the beginning
of the value, i.e. %MARTIN or %MARTIN%.
But if it is at the end of the value, i.e. MARTIN%, then index can be used
by the query optimizer.

This obviously would limit any hits to the beginning of the record so it may
not be of much practical use in this particular situation.

Elvis

-----Original Message-----
Subject: Re: fast search or scan for words in selected field

You would want an index where that one field is the first key field.
I was thinking of multiple selection criteria:
But if I want to search by name, and/or state, and/or phone#, and/or
contact name, then you would want multiple indexes. One of the
slower things to do is have the optimizer build a temp index.
In the above search, which ever search is for an exact match
(STATE = 'FL') should always be before a LIKE selection, so
select custid from cusmas where state = 'FL' and name like '%MARTIN%'
is much faster than
select custid from cusmas where name like '%MARTIN%' and state = 'FL'
(i'm not aware sql will reorder your select string, although they talked
about
big improvements in v5r3 sql performance).
btw- need to properly do name selects when apostrophe in name like O'HENRY.
C* Add an extra single quote (BJ'S  =  BJ''S)

Speed - I think it's very fast, but based on cpw. The system understands
repetetive
searching, so your calling a test once may be slower than 5 people each
calling the
search multiple times.
jim