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 !
