At 11:02 AM 30/10/2012, roydamman wrote:

>Hello,
>
>I have table with an indexed field (Firebird 2.1/2.5). When I use the query:
>
>select * from mytable where myfield like 'test'
>
>the plan uses the index on myfield and the query returns quickly.
>
>When I use the query:
>
>select * from mytable where myfield like :myparameter
>
>and define myparameter = 'test'
>
>the plan doesn't use the index (natural) and my query returns slowly.
>
>The big question is: What am I doing wrong? Any help is appreciated.

Don't use LIKE for an equivalence query. The purpose of LIKE is to pass a string with wildcard characters (_ or % in SQL). Your literal query self-converts to an equivalence query, viz.,

select * from mytable where myfield = 'test'
or to a STARTING WITH query
select * from mytable where myfield starting with 'test'

Both of these operators use indexes if they are available.

Your parameterised query is prepared in anticipation of a string that starts or ends (or both) with a wildcard, e.g., test%, %test or %test%. In preparing this query the engine does not know what it will get in the parameter. It can't speculatively prepare a plan that anticipates the possibility that it will get a literal with no wildcards (like your example) or test% (which it can convert to STARTING WITH) - so the plan is stuck with LIKE's inability to use an index.

./heLen

>Regards,
>
>Roy Damman
>
>
>
>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>Yahoo! Groups Links
>
>
>