Query Optimization
- Query optimization is the process of improving the performance of database queries to reduce execution time, resource usage, and ensure scalability. It is critical for high-performance applications, especially as data grows. Modern databases provide tools and strategies to analyze and tune queries.
Deep EXPLAIN / ANALYZE
-
The purpose is to understand how the database executes a query and identify bottlenecks, inefficient joins, scans, and index usage.
-
EXPLAIN shows the planned execution path for a query.
-
EXPLAIN ANALYZE executes the query and shows actual runtime metrics, including row counts and timing.
EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'Canada';
-- TODO: Show MySQL output
- If the output shows a sequential scan on a large table, consider adding an index or rewriting the query.
Query Rewriting for Performance
-
Rewrite queries to help the optimizer generate a better execution plan.
-
To improve performance, apply the techniques below:
-
Simplify complex joins.
-
For subqueries use EXISTS instead of IN.
-
Avoid SELECT * when not needed.
-
Aggregate earlier to reduce row counts.
-
Less efficient query, by default the country field from the customers table does not have an index.
SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Canada');
-- TODO: Show MySQL output
More efficient, JOIN allows the database to use indexes efficiently, because customer id and country id are primary keys and primary keys by default are created with an index.
SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'Canada';
-- TODO: Show MySQL output
Index-Only Scans
-
A scan where the query can be satisfied entirely from an index without accessing the table rows.
-
Greatly improves performance since reading the table data is avoided.
-
This done by creating an index to all selected columns.
This query fetches customer_id, order_date, total, Specifying the columns will reduce i/o.
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 101;
-- Solution is to create an index for the following columns customer_id, order_date, total. Indexes speeds up query execution. Ordering of the fields matter.
CREATE INDEX idx_customer_date_total ON orders(customer_id, order_date, total);
Common Performance Anti-Patterns
-
SELECT * - Wildcard usage
- Pulling all columns increases I/O unnecessarily.
SELECT * FROM orders; -- Bad, returns all the columns
SELECT id, order_date FROM orders; -- Better, returns only the columns needed
-
Functions on indexed columns
- Using function on indexed columns will prevent index usage.
-- Assuming order_date has an index, this is bad because it uses the DATE function
SELECT * FROM orders WHERE DATE(order_date) = '2025-11-26';
-- It is better to use comparizon operators to compare dates rather than to use a function
SELECT * FROM orders WHERE order_date >= '2025-11-26' AND order_date < '2025-11-27';
-
OR conditions instead of UNION
- Using OR can prevent index usage.
-- Assuming status has an index, this is slower because of the OR
SELECT * FROM orders WHERE status='pending' OR status='shipped'; -- Same plan
SELECT * FROM orders WHERE status IN ('pending', 'shipped'); -- Same plan
-- UNION ALL will combine the results of both queries and this is faster because it does not use OR
SELECT * FROM orders WHERE status='pending'
UNION ALL
SELECT * FROM orders WHERE status='shipped';
-
Non-sargable queries
- Are queries that cannot use indexes effectively.
WHERE YEAR(order_date) = 2025; -- Bad
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'; -- Good
-
Missing indexes on foreign keys or frequently filtered columns
- Can lead to full table scans and slow joins.
-
Analyzing execution plans (EXPLAIN / ANALYZE).
-
Rewriting queries for efficiency like JOINs, sargable conditions, and no SELECT *.
-
Leveraging index-only scans to minimize table access.
-
Avoiding common anti-patterns.