SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers...

This is, hands down, the best internet resource I’ve come across for learning more advanced SQL elements. I am learning so much.

For example, you have an index on last_name, and you run this:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE UPPER(last_name) = UPPER('winand')

You’d think that the where clause would utilize the index, but it doesn’t.

Although there is an index on LAST_NAME, it is unusable—because the search is not on LAST_NAME but on UPPER(LAST_NAME). From the database’s perspective, that’s something entirely different.

But!! You can create a computed field that contains that function, if you need it:

ALTER TABLE employees
      ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));

CREATE INDEX emp_up_name ON employees (last_name_up);
1 Like