In this lesson, you will learn how to structure your SQL queries to ensure the database engine can utilize indexes, drastically reducing the amount of data it needs to scan.
The Problem: Making the Database "Blind"
When you write a query, you might assume the database will always be smart enough to find your data instantly. However, if you perform calculations or apply functions to a column within your WHERE clause, you often "hide" that data from the index. This forces the database to perform a Full Table Scan, which means reading every single row on the disk—an expensive operation in terms of I/O.
Writing "SARGable" Queries
In database terminology, we want our queries to be SARGable (Search ARGumentable). This simply means the query is written in a way that allows the optimizer to use an index to jump directly to the relevant rows.
Consider a table orders with an index on order_date. If you want to find orders from the year 2023, avoid wrapping the column in a function:
-- BAD: This prevents index usage because the function must run on every row
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
Because the database has to calculate the YEAR() for every single entry in the table before it can check if it matches "2023," it ignores your index entirely. Instead, use a range-based approach:
-- GOOD: This allows the database to perform an index seek
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2024-01-01';
In the second version, the database can look at the index structure, jump straight to the first entry of 2023, and stop reading as soon as it hits 2024. By using raw column values rather than manipulating them, you save the database from performing thousands of unnecessary disk reads.
Beyond Dates
This logic applies to strings and numbers too. Avoid using leading wildcards like LIKE '%term', which forces a scan, and avoid mathematical operations on columns in the WHERE clause (e.g., WHERE price * 1.1 > 100). Always try to isolate the column on one side of the operator so the index remains "visible" to the query optimizer.
Key Takeaway: Keep your indexed columns "naked" in the WHERE clause—never wrap them in functions or calculations—to ensure the database performs an efficient index seek instead of a slow full table scan.