On 1/31/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Gilles <[EMAIL PROTECTED]> wrote: > > At 15:40 31/01/2008 -0800, James Dennett wrote: > > > > WHERE col LIKE '123%' or WHERE substr(col,1, 3) = '123' > > > > > >The optimizer has a decent chance of using an index for LIKE '123%' but > > >I'd be surprised (and impressed) if it looks inside function calls suchas > > >substr for opportunities to use indexes. > > > > Thanks guys. I'll remember to index the column, for higher performance. > > > > Indices won't help with LIKE unless the column as a NOCASE > collation. Use GLOB instead: > > ... WHERE number GLOB '1234*'; > > Note that "*" is the wildcard character with GLOB, not "%" > as in LIKE. The above will use an index on the number column > if it is available.
if GLOB is so good, I am curious what are the instances in which one would prefer col LIKE '123%' over col GLOB '123*' ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
