Em 29/10/2012 20:02, roydamman escreveu:

> 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.
>
> Regards,
>
> Roy Damman
>

Once it's a parameter, the parameter could hold any value like:
'ABC'
'ABC%'
'%ABC'

the first two could use an index during a search the last on could not
use any index.

The optimizer shoul prepare the path for all the scenarios.

You could change your query to something like:

select * from mytable where myfield starting with :myparameter

This way an index would be used.

In fact in a non parameterized query, your original query has a kind of code injection, and become something like:

select * from mytable where myfield like 'test%'
and myfield starting with 'test'

Thus the index could be used.

see you !