Query Optimization
SQL · Extra Concepts
Theory
Query optimization means making a query return the same result while doing less work: reading fewer rows, fewer columns and less data from disk or the network. Most gains come from a few habits rather than clever tricks.
First, keep filters sargable (search-argument-able): write WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01' instead of WHERE YEAR(hire_date) = 2024, because wrapping the column in a function hides it from the index. Second, select only the columns you need instead of SELECT *, which matters a lot in columnar warehouses. Third, filter early, before joins and aggregations, so later steps handle fewer rows. Fourth, add indexes on the columns used in filters and joins, and check the execution plan to confirm they are used. Other common fixes: avoid leading wildcards like LIKE '%kumar', and use UNION ALL instead of UNION when duplicates are impossible, to skip a costly de-duplication step.
Why we use it
Slow queries waste time, block other users and, in cloud warehouses, cost money for every byte or second used. Optimizing gives the same answer faster and cheaper.
When to use it
Optimize queries that run often, run on large tables or feed dashboards. Measure first with the execution plan, change one thing at a time and re-measure. Do not spend effort tuning a one-off query that already runs in seconds.
How it works
- A query is too slow
- Read the execution plan to find the costliest step
- Function on a filtered column? Yes: Rewrite as a sargable range. No: Check filter/join indexes.
- Select only needed columns; filter before joins
- Re-run the plan and compare timings
- Keep the change if the result is identical and faster
Syntax
-- techniques, not a single statementExample
-- Instead of a full scan with a function on the column:
-- WHERE YEAR(hire_date) = 2024
-- Prefer a sargable range so an index can be used:
SELECT * FROM employees
WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01';Key points
- Avoid wrapping indexed columns in functions ("sargability")
- Select only the columns you need instead of SELECT *
- Filter as early as possible, before joins and GROUP BY
- Use UNION ALL instead of UNION when duplicates cannot occur
- Measure with the execution plan before and after every change