DB: firebird 2.5.4
I have 1 table, 2 string fields and 1 computed field:
Files name varchar 256 extension varchar 4 filename computed by name||extension
I want to search a filename in this table (case insensisive)
The query is
Select * from files f where upper(f.filename) = upper('test.txt')
This is working of course, and to speed up the query, I created a computed index on files on
CREATE INDEX test ON FILES COMPUTED BY (upper(filename));
Now, the same query doesn't work anymore ! It returns nothing. I tried an index on lower, but it doesn't work either.
wtf? Did I miss an option somewhere?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM files f WHERE upper(f.filename) = upper('test.txt')