SQL Indexes: The Ultimate Guide to Query Optimization

-- io.thecodeforge.optimization
-- Scenario: Searching for a customer without an index
SELECT * FROM io_thecodeforge.orders WHERE customer_id = 1234;
-- Result: The database performs a 'Sequential Scan' (reads 100% of the table).

-- Optimization: Create a standard B-tree index
CREATE INDEX idx_orders_customer 
ON io_thecodeforge.orders(customer_id);

-- Now the query engine performs an 'Index Scan'
EXPLAIN ANALYZE 
SELECT * FROM io_thecodeforge.orders WHERE customer_id = 1234;

-- io.thecodeforge.optimization
-- Composite index on (last_name, first_name)
CREATE INDEX idx_user_full_name 
ON io_thecodeforge.users(last_name, first_name);

-- VALID: Uses index (leftmost prefix)
SELECT * FROM io_thecodeforge.users WHERE last_name = 'Jordan';

-- VALID: Uses index (exact match)
SELECT * FROM io_thecodeforge.users WHERE last_name = 'Jordan' AND first_name = 'Michael';

-- INVALID: Full Table Scan (skips the first column of the index)
SELECT * FROM io_thecodeforge.users WHERE first_name = 'Michael';

-- PRO TIP: Covering Index
-- This index contains ALL data needed, avoiding a 'Table Lookup' entirely
CREATE INDEX idx_covering_search 
ON io_thecodeforge.users(email) INCLUDE (user_id, status);

-- io.thecodeforge.maintenance
-- 1. Identifying unused indexes (PostgreSQL example)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND idx_read = 0;
-- Action: Drop these to reclaim write performance.

-- 2. Avoiding 'Functional' index traps
-- This ignores a standard index on 'created_at'
SELECT * FROM io_thecodeforge.orders WHERE DATE(created_at) = '2024-01-01';

-- Correct approach: Use a range or a functional index
CREATE INDEX idx_functional_date ON io_thecodeforge.orders ((DATE(created_at)));