Em 15/1/2014 10:37, Christian Kusenbach escreveu:

> Hi,
>
> I have a question about computed indices related to integer fields.
>
> In my program there are several selects on integer-fields with a STARTING WITH clause.
>
> Firebird internally converts the integer value to a string and then filters the value.
> It would be great if I could create a computed index on that (internal) string so FB does an index-lookup for the data.
>
> I tried to create an computed index on a table with the expression "CAST(MY_INT_FIELD AS VARCHAR(11))" cause I think firebird internally uses a VARCHAR(11) or CHAR(11) value but that doesn't solve the problem.
>
> Any idea on how to get this working without changing every select in the program?
>
> Thanks and best regards!
> Christian
>

I think you meant expression index instead of compound index....

The expression index will be used if the search criteria is the same as
the expression index:

your example:

select * from MyTable where MyIntCollumn starting with '1'

you created an expression index as
Cast(MyIntColumn as varchar(11))

so, your query should be:
select * from MyTable where Cast(MyIntCollumn as varchar(11)) starting
with '1'


So there is no way you could use the expression index without rewriting
your query.

I have no idea why you are doing this kind of select but it lookslike
weird to me... And since you will need to rewrite the query, perhaps
could be a good idea to write it in a better way (in the last case, use
a mirror collumn updated by a after insert/update trigger with the int
value formated as you wish to search for).

see you !