Suggest an edit

Improve this article

Refine the answer for “What is a "functional index"?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

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:

```sql
SELECT * FROM users WHERE LOWER(email) = 'test@mail.com';
```

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

The fix, a functional index:

```sql
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));
```

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
  CREATE INDEX idx_products_left_code
  ON products (LEFT(code, 3));
  ```
- An index on a computed expression:

```sql
  CREATE INDEX idx_orders_total
  ON orders (price * quantity);
  ```
- An index on a date without the time:

```sql
  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.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts245 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.