EverSQL Database Performance Knowledge Base

Question 1

Using computed upper index in firebird to compare case insensitive strings

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 upper(filename)

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?

Answer

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

Avoid Calling Functions With Indexed Columns (query line: 6): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `filename` is indexed, the index won’t be used as it’s wrapped with the function `upper`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.

SELECT
        * 
    FROM
        files f 
    WHERE
        upper(f.filename) = upper('test.txt')

Question 2