MySQL Indexes and Performance

Without index: Full table scan → read ALL rows → O(n)
With index:    B-tree lookup    → read ~20 pages → O(log n)

1 million rows:
  Full scan:  ~1,000,000 page reads
  With index: ~20 page reads

-- Single column (most common)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created ON orders(created_at);

-- Unique index (enforces uniqueness + fast lookup)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite index (multi-column)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Use order: most selective first, or match your most common WHERE clause

-- Partial index (MySQL 8.0.13+)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;

-- Covering index (includes all columns a query needs)
CREATE INDEX idx_cover ON orders(user_id, created_at, amount);
-- SELECT amount FROM orders WHERE user_id = 1 ORDER BY created_at
-- Can be answered entirely from the index (no table lookup)

-- ✅ INDEX these:
-- 1. Primary keys (automatic)
-- 2. Foreign keys (JOIN columns)
-- 3. WHERE clause columns
-- 4. ORDER BY columns
-- 5. GROUP BY columns
-- 6. Columns with high cardinality (many unique values)

-- ❌ DON'T index:
-- 1. Small tables (< 1000 rows)
-- 2. Low-cardinality columns (boolean, status with few values)
-- 3. Columns rarely used in queries
-- 4. Frequently written columns (index maintenance cost)
-- 5. Text/blob columns without prefix specification

-- See how MySQL executes a query
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- Key columns in EXPLAIN output:
-- type: system > const > eq_ref > ref > range > index > ALL
--   ALL = bad (full table scan)
--   ref = good (uses index)
--   const = excellent (single row lookup)

-- key: which index MySQL chose to use
-- rows: estimated rows scanned
-- Extra: Using index (covering), Using filesort (bad)

-- Detailed analysis
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- ❌ BAD: Function on indexed column (prevents index use)
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ GOOD: Range on raw column
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ BAD: Leading wildcard
SELECT * FROM users WHERE name LIKE '%son';

-- ✅ GOOD: Full-text index for text search
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('son');

-- ❌ BAD: SELECT * (fetches unnecessary data)
SELECT * FROM orders WHERE user_id = 123;

-- ✅ GOOD: Select only needed columns
SELECT id, product, amount, created_at FROM orders WHERE user_id = 123;

-- ❌ BAD: OR on different columns
SELECT * FROM users WHERE email = 'a@m.com' OR city = 'NYC';

-- ✅ GOOD: UNION ALL
SELECT * FROM users WHERE email = 'a@m.com'
UNION ALL
SELECT * FROM users WHERE city = 'NYC' AND email != 'a@m.com';

-- Check index usage
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

-- Analyze table statistics
ANALYZE TABLE users;

-- Optimize table (reclaim space, rebuild indexes)
OPTIMIZE TABLE users;

-- Show index information
SHOW INDEX FROM users;

-- Monitor slow queries
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries > 1 second