Avoiding Full Table Scans
A full table scan reads every row in the table - O(N) time. On a table with millions of rows this takes seconds. Identifying and eliminating full table scans is the most impactful performance optimization. Common causes and fixes are well-established patterns.
Full Scan Causes & Fixes
- No index on WHERE/JOIN column → CREATE INDEX
- Function on indexed column (YEAR(), LOWER()) → rewrite query to use range
- Leading wildcard LIKE '%text' → use Full-Text Search or avoid
- Low selectivity column (status with 3 values on 1M rows) → partial index or composite
- NOT IN with NULL values → use NOT EXISTS
- OR on different columns → use UNION
- Small tables (< 1000 rows) - optimizer may prefer seq scan even with index (acceptable)
Avoiding Full Scan Examples
-- BEFORE: full table scan (no index)
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- Seq Scan on orders (cost=0.00..24.00 rows=1000)
-- Full scan reading all 1000 rows!
-- Fix: add index
CREATE INDEX idx_orders_user_id ON orders(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
-- Index Scan using idx_orders_user_id
-- BEFORE: function prevents index use
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;
-- Seq Scan (function on indexed column!)
-- FIX: range condition on raw column
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- Index Scan on created_at
-- BEFORE: low-selectivity alone
SELECT * FROM orders WHERE status = 'pending';
-- Optimizer may choose Seq Scan (status has only 5 values)
-- FIX: combine with high-selectivity column in composite index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
-- Index Scan on composite index (highly selective)Quick Quiz
Tip
Tip
Practice Avoiding Full Table Scans in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with Avoiding Full Table Scans 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 Avoiding Full Table Scans 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
- A full table scan reads every row in the table - O(N) time.
- No index on WHERE/JOIN column → CREATE INDEX
- Function on indexed column (YEAR(), LOWER()) → rewrite query to use range
- Leading wildcard LIKE '%text' → use Full-Text Search or avoid