Indexing an expression in SQL

You cannot index a column and then search a function of it. Index the function instead.

CREATE INDEX customers_email_lower
    ON customers (LOWER(email));

EXPLAIN QUERY PLAN
SELECT
    id,
    name
FROM customers
WHERE LOWER(email) = 'ada@example.com';

How it works

1. An expression index stores the computed value, so the search can use it.
2. The query has to spell the expression exactly the way the index does.
3. The expression must be deterministic — no CURRENT_TIME, no RANDOM.

Keywords and builtins used here

• CREATE
• EXPLAIN
• FROM
• INDEX
• LOWER
• ON
• SELECT
• WHERE

The run, in numbers

Lines

9

Characters to type

154

Tokens

29

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 19 seconds.

Type this snippet

Step 4 of 5 in Indexes, step 6 of 17 in Plans & performance.

← Previous Next →