I second that

Regards Bogdan

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison
Sent: Tuesday, October 30, 2012 3:36 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Parameterised like query won't use index
in the plan


On Tue, Oct 30, 2012 at 4:50 AM, Kjell Rilbe <kjell.rilbe@...
<mailto:kjell.rilbe%40datadia.se> > wrote:

>
> ... if it contains no wildcards (% and _), use: where myfield =
> :myparameter ...
> If it contains a single % wildcard at the end...
> If it contains multiple wildcards, but none at the beginning...
> .If the search string starts with a wildcard, resort to this:
> where myfield like :myparam
>
> Now, I wonder if something similar could actually be done internally by
> Firebird for the general like case? It could some nifty query logic like
> the above internally, could it not? If "like xxx" always generated
> internally a query logic like this:
>
> where myfield starting with <prefixof :param up to but not including
> first wildcard>
> and myfield like :param
>
> I assume starting with will return true for all data values if the
> prefix is an empty string - is this true? In that case, would this
> internal query return the same results as the direct/simple "myfield
> like :param" query? And would it actually give better performance in
> general? Would it risk significantly worse performance in any situation?
>

Since a parameterized query is compiled and optimized only once and reused,
that logic would have to be in the execution plan. A goal of execution
plans is
to be dirt simple, adding parsing and string analysis would be unnatural.
I'm
not saying it couldn't be done, but the existing data structures - which
are built
as they are for a reason - cannot handle that complexity.

Good luck,

Ann

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]