[firebird-support] Parameterised like query won't use index in the plan
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
Re: [firebird-support] Parameterised like query won't use index in the plan
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 !
Re: [firebird-support] Parameterised like query won't use index in the plan
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
