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 !
