Re: [firebird-support] CONTAINIG vs. LIKE
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
Re: [firebird-support] CONTAINIG vs. LIKE
Mark, thanks for your answer. (the page number I mentioned was on the first edition ). Aldo Caruso El 04/08/14 a las 11:50, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] escibió: 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 ++ 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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] CONTAINIG vs. LIKE
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 * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
