Introduction
Query optimization is the process of writing SQL and structuring schemas so the database engine's query planner can find an efficient way to execute a statement. The planner (optimizer) evaluates possible strategies — sequential scans, index scans, different join orders and algorithms — and estimates the cost of each based on table statistics, then picks the cheapest plan it can find. Understanding a few core principles helps you write queries that give the optimizer good options instead of forcing it into slow paths.
Cricket analogy: A captain reviewing field placements before each ball is like the query planner weighing scan versus index-scan strategies and picking the cheapest one, the way Rohit Sharma sets a field based on the batter's tendencies, not a fixed default.
Syntax
-- Bad: wrapping the indexed column in a function prevents index use
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- Good: rewrite as a sargable range condition
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- Check estimated cost before and after rewriting
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';Explanation
A predicate is called 'sargable' (Search ARGument ABLE) when the optimizer can use an index to evaluate it directly, without transforming every row's value first. Wrapping an indexed column in a function or arithmetic expression (e.g., YEAR(created_at), price * 1.1 > 100) usually makes the predicate non-sargable, because the engine would have to compute the function for every row before it could compare — defeating the purpose of the index. Other common optimization principles include: select only the columns you need instead of SELECT *, so less data is read and possibly a covering index can be used; filter as early and precisely as possible so fewer rows flow into joins and aggregations; and avoid implicit type conversions between compared columns, which can also block index usage.
Cricket analogy: Wrapping YEAR(created_at) in a query is like a scorer recalculating every ball's run-rate by hand instead of reading the pre-tallied scoreboard, forcing a full table scan instead of using the sargable index.
Example
-- Non-sargable: LIKE with a leading wildcard cannot use a standard B-tree index
SELECT customer_id, name FROM customers WHERE name LIKE '%smith%';
-- Sargable: prefix search can use an index
SELECT customer_id, name FROM customers WHERE name LIKE 'Smith%';
-- Non-sargable: implicit conversion (order_id stored as INT, compared to a string)
SELECT * FROM orders WHERE order_id = '4821';
-- Sargable: matching types
SELECT * FROM orders WHERE order_id = 4821;Analysis
In the LIKE example, a leading wildcard ('%smith%') means a match could start anywhere in the string, so a standard sorted index cannot narrow the search range — the engine must inspect every value. A trailing wildcard ('Smith%') preserves a sortable prefix, so the optimizer can jump straight to the 'Smith' range in the index. Similarly, comparing an INT column to a string literal forces an implicit conversion that can silently disable index usage on some engines. These small rewrites do not change what the query returns, only how efficiently the optimizer can execute it — which is the essence of query optimization.
Cricket analogy: Searching player names with '%smith%' forces the scorer to check every entry from A to Z, but 'Smith%' lets them jump straight to the S section of the alphabetically sorted scorecard, just like a trailing wildcard preserves index range.
Key Takeaways
- A sargable predicate is one the optimizer can evaluate using an index directly.
- Wrapping an indexed column in a function or expression usually makes a predicate non-sargable.
- Leading-wildcard LIKE patterns ('%value') cannot use a standard B-tree index; trailing wildcards ('value%') can.
- Avoid implicit type conversions between compared columns, as they can block index usage.
- Select only needed columns and filter as early as possible to reduce the data the optimizer must process.
Practice what you learned
1. What does it mean for a predicate to be 'sargable'?
2. Which WHERE clause is most likely to prevent index usage on created_at?
3. Why does 'LIKE %smith%' typically fail to use a standard B-tree index, while 'LIKE Smith%' can?
4. What is a general best practice for reducing query cost besides indexing?
Was this page helpful?
You May Also Like
Indexes and How They Work
How database indexes use B-tree structures to speed up lookups, and why they add overhead to writes.
Execution Plans
How to read EXPLAIN output to understand and diagnose how the database will run a query.
Indexing Strategies and Tradeoffs
How to choose composite index column order, use covering indexes, and know when not to index.