In this lesson, you will learn why wrapping a database column in a function often causes the engine to ignore your carefully crafted indexes, resulting in slow performance.
When we create an index (like a B-Tree), the database stores the column values in a specific, sorted order. This allows the engine to jump straight to the data we need without scanning every row. However, this optimization relies on the engine being able to compare the raw value in the index to the value you provided in your query.
The "Hidden" Problem
Index suppression occurs when you perform a calculation or apply a function to a column in your WHERE clause. Imagine you have an index on a created_at column. If you write a query like this:
SELECT * FROM orders
WHERE YEAR(created_at) = 2023;
The database cannot simply look up 2023 in the created_at index. Why? Because the index contains raw timestamps (e.g., 2023-05-12 10:00:00), not the result of the YEAR() function. To fulfill your request, the database is forced to calculate the year for every single row in the table before it can perform the comparison. This process is called a "Full Table Scan," and it completely bypasses the B-Tree structure you built.
The Fix
To restore performance, you must rewrite the query to isolate the indexed column so the engine can perform a "range scan" instead:
-- This allows the engine to use the index!
SELECT * FROM orders
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
By keeping the column "naked" on one side of the operator, you allow the database to navigate the index tree, find the starting point, and stop exactly when it hits the end of the year.
Whenever you see a function, mathematical operation, or type casting wrapping a column in a WHERE clause, assume you are potentially suppressing the index and look for a way to rewrite the logic to keep the column raw.
Key Takeaway: Never wrap an indexed column in a function or calculation within your WHERE clause, as it forces the database to ignore the index and scan the entire table.