Indexes Overview

-- PRIMARY KEY (clustered index): the table IS the index
-- Rows stored in PK order on disk; all secondary indexes include PK
CREATE TABLE orders (
    id         BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- clustered
    ...
);

-- Single-column secondary index: speeds up equality and range on one column
CREATE INDEX idx_status ON orders (status);

-- Composite (multi-column) index: leftmost prefix rule applies
-- Covers: status, status+customer_id, status+customer_id+created_at
CREATE INDEX idx_status_customer_date ON orders (status, customer_id, created_at);

-- UNIQUE index: enforces uniqueness + fast lookup (same as regular index + constraint)
CREATE UNIQUE INDEX idx_email ON users (email);

-- FULLTEXT index: relevance-based text search with MATCH() ... AGAINST()
CREATE FULLTEXT INDEX idx_description ON products (description);
SELECT * FROM products WHERE MATCH(description) AGAINST ('wireless headphones' IN BOOLEAN MODE);

-- Prefix index: index only first N characters (saves space for long VARCHAR)
CREATE INDEX idx_url_prefix ON pages (url(50));
-- Cannot be used for ORDER BY or GROUP BY (not covering)

-- EXPLAIN output: key to diagnosing index usage
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND customer_id = 42;
-- Look for:
-- type: ALL (full scan — bad), ref (index lookup — good), range, const
-- key: which index was chosen (NULL = no index)
-- rows: estimated rows scanned
-- Extra: "Using index" (covering), "Using filesort" (no sort index)

-- Index selectivity: high cardinality = better index
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders;
-- 0.001 = 0.1% (bad — status has few unique values)
-- 0.99  = 99% (good — almost every row has a unique value)

-- Rule of thumb for composite index column order:
-- 1. Equality predicates first (WHERE status = 'PENDING')
-- 2. Range predicates last (WHERE created_at > '2024-01-01')
-- 3. Sort column last if possible (ORDER BY created_at)

-- Check actual index usage in production
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND object_name = 'orders'
ORDER BY count_read DESC;
-- Indexes with count_read=0 are unused — candidates for removal

-- InnoDB clustered index: table data IS the B-tree
-- PK row: [id=1 | customer_id=42 | status='PENDING' | total=99.99]

-- Secondary index on (status):
-- Leaf node: [status='PENDING' | id=1]  ← stores PK, NOT physical row address
-- Extra lookup: to get full row from secondary index:
--   1. Look up status='PENDING' in secondary index → get PK=1
--   2. Look up PK=1 in clustered index → get full row
-- This is a "double lookup" or "row fetch" — avoided by covering indexes

-- UUID PK problem: random insertion order
-- [INSERT id=uuid-3] → page split (new page needed between existing pages)
-- [INSERT id=uuid-1] → another split
-- Page splits cause fragmentation, wasted space, slower inserts

-- Solution: use BIGINT AUTO_INCREMENT (sequential, append-only)
-- Or: UUID v7 (time-ordered) / ULID (lexicographically ordered) if UUID is needed

-- Measure fragmentation
SELECT table_name,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(data_free/1024/1024, 2)   AS free_mb,
       ROUND(data_free/(data_length+index_length)*100,1) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'orders';

-- Rebuild to defragment (locks table!)
ALTER TABLE orders ENGINE=InnoDB;  -- or: OPTIMIZE TABLE orders;