Query Optimization Basics
Query optimization is the practice of writing SQL and designing schemas so queries run as fast as possible. Before adding indexes, understand what makes queries slow: full table scans, unnecessary columns, missing indexes on JOINs, wildcard LIKE searches.
Common Performance Issues
- SELECT * - retrieves unnecessary columns, bloats network transfer
- WHERE col+0 = 5 - function on column prevents index use
- WHERE LIKE '%text' - leading wildcard prevents index scan
- Missing index on JOIN columns - full table scan on every join
- Large OFFSET in pagination - DATABASE must scan and skip all offset rows
- N+1 queries - fetching related data row-by-row in a loop (application code issue)
- No LIMIT - returning millions of rows when only 10 are needed
Query Optimization Examples
-- BAD: function on column prevents index
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- GOOD: use range instead
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- BAD: leading wildcard - full table scan
SELECT * FROM products WHERE name LIKE '%phone%';
-- GOOD for exact prefix:
SELECT * FROM products WHERE name LIKE 'Phone%';
-- Or use Full-Text Search for contains search
-- BAD: SELECT * in JOINs (fetches all columns from all tables)
SELECT * FROM orders JOIN users ON orders.user_id = users.id;
-- GOOD: select only needed
SELECT o.id, o.status, u.name, u.email
FROM orders o JOIN users u ON o.user_id = u.id;
-- BAD: large OFFSET pagination
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- GOOD: cursor-based (keyset) pagination
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
-- BAD: OR on different columns (hard to optimize)
SELECT * FROM users WHERE name = 'John' OR email = 'john@example.com';
-- GOOD: UNION (each part can use its own index)
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE email = 'john@example.com';Quick Quiz
Tip
Tip
Practice Query Optimization Basics in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
EXPLAIN ANALYZE first! Index wisely.
Common Mistake
Warning
A common mistake with Query Optimization Basics is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Query Optimization Basics from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Query optimization is the practice of writing SQL and designing schemas so queries run as fast as possible.
- SELECT * - retrieves unnecessary columns, bloats network transfer
- WHERE col+0 = 5 - function on column prevents index use
- WHERE LIKE '%text' - leading wildcard prevents index scan