What is a "functional index"?

A functional index is an index built not on the column's raw value, but on the result of a function or expression applied to that column.

It speeds up queries where WHERE or ORDER BY uses a computation, instead of a direct field comparison.

#Example

Without an index:

If a plain index exists on email, it won't be used, because the query involves the LOWER() function.

The fix, a functional index:

Now the DBMS stores LOWER(email) values in the index and can use it for queries like this.

#Other examples

• An index on part of a string:

  sql

  Copy

  CREATE INDEX idx_products_left_code ON products (LEFT(code, 3));

• An index on a computed expression:

  sql

  Copy

  CREATE INDEX idx_orders_total ON orders (price * quantity);

• An index on a date without the time:

  sql

  Copy

  CREATE INDEX idx_logs_date ON logs (DATE(created_at));

#Advantages

• Speeds up queries with functions, expressions, casts.
• Reduces the need to store redundant "precomputed" fields.

#Limitations

• Only gets used when the expression in the query matches exactly the expression in the index.
• Adds overhead on insert and update (same as regular indexes).

Summary: A functional index is a "smart" index that stores not the raw data, but the results of computations over it, so it can speed up queries that use functions and expressions in their conditions.