56 SQL Queries That Will Destroy Your Database (Part 4 of 56)

This is Query #4 in the series, and it focuses on a very common performance issue: applying functions to indexed columns in the WHERE clause, which prevents the database from using indexes efficiently.

❌ The slow query

SELECT order_id, order_date, total_amount
FROM orders
WHERE YEAR(created_at) = 2024;

SELECT order_id, order_date, total_amount
FROM orders
WHERE YEAR(created_at) = 2024;

SELECT order_id, order_date, total_amount
FROM orders
WHERE YEAR(created_at) = 2024;

What's happening?

Imagine your orders table has 10 million rows.

For every row, the database has to:

1. Read the created_at date.

2. Extract the year using YEAR().

3. Check if the year is 2024.

It repeats this 10 million times.

Why is it slow?

Suppose created_at has an index.

An index is like the index at the back of a book. Instead of reading every page, you can jump directly to the information you need.

But when you write:

YEAR(created_at)

YEAR(created_at)

YEAR(created_at)

you're asking the database to change the value first before comparing it.

The index stores values like:

2023-12-30
2023-12-31
2024-01-01
2024-01-02
2024-01-03.

2023-12-30
2023-12-31
2024-01-01
2024-01-02
2024-01-03.

2023-12-30
2023-12-31
2024-01-01
2024-01-02
2024-01-03.

It does not store:

2023
2023
2024
2024
2024

2023
2023
2024
2024
2024

2023
2023
2024
2024
2024

So the database can't use the index to quickly find 2024 records. Instead, it has to examine every row and calculate YEAR(created_at) one by one.

✅ The faster query

SELECT order_id, order_date, total_amount
FROM orders
WHERE created_at >= '2024-01-01'  AND created_at < '2025-01-01';

SELECT order_id, order_date, total_amount
FROM orders
WHERE created_at >= '2024-01-01'  AND created_at < '2025-01-01';

SELECT order_id, order_date, total_amount
FROM orders
WHERE created_at >= '2024-01-01'  AND created_at < '2025-01-01';

Why is this faster?

Now you're comparing the original created_at values directly.

The database can use the index to:

• Jump straight to 2024-01-01

• Read rows until 2025-01-01

• Ignore everything before and after

Instead of scanning the whole table, it only reads the rows that match the date range.

Real-world analogy

Imagine a library.

❌ Using YEAR(created_at)

You ask:

"Check the publication year of every book and tell me which ones are from 2024."

The librarian has to pull every book off the shelf and look inside.

✅ Using a date range

You ask:

"Show me all books filed between January 1, 2024 and December 31, 2024."

The librarian can go directly to the correct shelf because the books are already organized by date.

The general rule

Avoid applying functions to indexed columns in a WHERE clause.

For example, these can prevent the database from using an index:

YEAR(created_at)
LOWER(email)
UPPER(name)
DATE(created_at)
CAST(price AS INT)

YEAR(created_at)
LOWER(email)
UPPER(name)
DATE(created_at)
CAST(price AS INT)

YEAR(created_at)
LOWER(email)
UPPER(name)
DATE(created_at)
CAST(price AS INT)

Instead, write conditions that compare the original column value whenever possible.

One-line rule:

Indexes work on the original data. If you change the column with a function, the database usually can't use the index efficiently.