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 !
