> CREATE INDEX MyUDFIndex ON substr(a.zone,1,4)
>
> and I doubt (I haven't tried, and would be happy if I'm wrong) that
> index could be used with substr(a.zone,1,3).
>
> Set

One of the conceptual problems of attempting to use an index against
an external function is there is no guarantee provided by the UDF that
for any given input, a predictable and consistent result will be returned.

How would you index the results of GetExactTimestamp or something like
that? What about a function like rand(n) that accepted an integer and
returned a random number between 0 and n?

rand(50) might return 20 one call and 38 the next, so such an index
would return incorrect results. In any case, I prefer to use standard
SQL where available, so go with either

a.zone Starting With 'EUR_'

or

a.zone Like 'EUR_%'

both of which will use an index if available. Containing can not use
an index.

Adam