"Containing" is not case sensitive and cannot use an index. But it could not be indexed anyway because thestring could occur anywhere in the field, not just at the start. "Starting" (or is it "starts"?) is case sensitive and equivalent to "like%" so it can use an index.
Good luck, Ann > On Aug 4, 2014, at 11:50 AM, "Mark Rotteveel m...@lawinegevaar.nl > [firebird-support]" <firebird-support@yahoogroups.com> wrote: > > On 4-8-2014 16:42, Aldo Caruso aldo.car...@argencasas.com > [firebird-support] wrote: >> Hello, >> >> I have three questions relating CONTAINING predicate. >> >> In "The Firebird Book" I read on page 318 that indexes are used >> also on search conditions against CONTAING predicates. I did some test >> and, inspecting its PLAN, I found that it uses NATURAL order instead of >> using an index. >> >> 1) ¿ Under which circumstances an index is used when the search has a >> CONTAINING condition ? >> >> Given the following two SQL clauses >> >> SELECT * FROM TABLE1 WHERE UPPER(FIELD1) LIKE '%TEST%' >> SELECT * FROM TABLE1 WHERE FIELD1 CONTAINING 'TEST' >> >> 2) Are they logically equivalent ? >> 3) Which of them is faster ? > > They are logically equivalent, and I'd assume they perform similar > (although a CONTAINING might have the benefit that it doesn't need to > support more complex patterns). I am not 100% sure, but I think Helen's > book is wrong here (btw: page 270 in The Firebird book second edition). > > Mark > -- > Mark Rotteveel > > > ------------------------------------ > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > ------------------------------------ > > Yahoo Groups Links > > >
