Re: fast search or scan for words in selected field

(i may be wrong on this) but I thought if an index exists
over the NAME field, and a ..where name like %JOHN%
then sql will search all the entries in that index, which was still faster
than reading that column in the file.
jim
----- Original Message ----- 
From: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Friday, September 10, 2004 2:16 PM
Subject: RE: fast search or scan for words in selected field


> 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
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>