What are SQL indexes? When should you use them?

Without index: Full Table Scan
→ Database reads every row to find WHERE email = 'alice@test.com'
→ 1,000,000 rows scanned

With index on email:
→ B-Tree lookup: O(log n) — finds the row in milliseconds
→ ~20 comparisons to find 1 row in 1,000,000

-- Single column index
CREATE INDEX idx_employees_email ON employees(email);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);

-- Composite index (multiple columns — order matters!)
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
-- Covers: WHERE customer_id = 5
-- Covers: WHERE customer_id = 5 AND status = 'completed'
-- Does NOT cover: WHERE status = 'completed' alone (leading column rule)

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial index (PostgreSQL) — index subset of rows
CREATE INDEX idx_active_orders ON orders(customer_id)
WHERE status = 'active';  -- only index active orders

-- Clustered index (Primary Key): rows physically sorted by this key
-- Only ONE per table. In MySQL InnoDB, PK is always clustered.
ALTER TABLE employees ADD PRIMARY KEY (id);

-- Non-clustered index: separate structure pointing to rows
-- Multiple allowed per table
CREATE INDEX idx_dept ON employees(department);

-- Covering index: includes all columns a query needs
-- No need to look up the main table at all
CREATE INDEX idx_covering ON orders(customer_id, status, total_amount);
-- Query: SELECT status, total_amount FROM orders WHERE customer_id = 5
-- ↑ Satisfied entirely by the index (index-only scan)

-- ✅ Good candidates for indexing:

-- 1. Columns in WHERE clause
SELECT * FROM orders WHERE customer_id = 123;  -- index on customer_id

-- 2. Columns in JOIN ON
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- index on o.customer_id AND c.id (PK usually auto-indexed)

-- 3. Columns in ORDER BY (eliminates sort step)
SELECT * FROM orders ORDER BY created_at DESC;  -- index on created_at

-- 4. Foreign key columns
-- Always index FK columns — JOINs on them are very common

-- 5. High-cardinality columns (many unique values)
-- email, user_id, order_id → great
-- status ('active'/'inactive') → low cardinality, less useful

-- ❌ Avoid indexes on:
-- 1. Small tables (full scan is fine, index overhead not worth it)
-- 2. Columns rarely used in WHERE/JOIN
-- 3. Tables with heavy INSERT/UPDATE/DELETE (each write updates all indexes)
-- 4. Low-cardinality columns (gender, boolean — not selective enough)

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
-- Look for: type = 'ref' or 'range' (good) vs 'ALL' (full scan — bad)
-- key column shows which index is used

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- Look for: Index Scan vs Seq Scan

-- Force index (MySQL)
SELECT * FROM orders USE INDEX (idx_customer_id) WHERE customer_id = 5;

-- Check indexes on a table
SHOW INDEX FROM orders;  -- MySQL

-- Find slow queries not using indexes
-- Enable slow query log in MySQL:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries > 1 second