Den 2012-10-30 08:45 skrev roydamman s�h�r:

>
>
> Hello Helen, you are right, not to use LIKE for a normal search. But
> LIKE was used to give the users of my programs, the most flexibel
> search options. It seems not a good practice.
>

You could analyze the search string entered by the user, and if it
contains no wildcards (% and _), use:
where myfield = :myparameter

If it contains a single % wildcard at the end, strip the wildcard char
and use:
where myfield starting with:myparameter

If it contains multiple wildcards, but none at the beginning, extract
the start of the search string up to but not including the first
wildcard and us that for param :myprefix in a query like this:
where myfield starting with :myprefix
and myfield like :myparam
This allows Firebird to use an index to reduce the like search to those
that at least match the prefix.

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?

Regards,
Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



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