Index Creation & Optimization

-- Create simple index
CREATE INDEX idx_email ON users(email);

-- Create unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Composite index (multiple columns)
CREATE INDEX idx_user_status 
ON orders(user_id, status);

-- Composite index with order
CREATE INDEX idx_user_date 
ON orders(user_id, created_at DESC);

-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users 
ON users(email) 
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_lower_email 
ON users(LOWER(email));

-- Full-text index (MySQL)
CREATE FULLTEXT INDEX idx_product_search 
ON products(name, description);

-- Spatial index (for geographic data)
CREATE SPATIAL INDEX idx_location 
ON stores(coordinates);

-- Drop index
DROP INDEX idx_email ON users;

-- View indexes
SHOW INDEXES FROM users;

-- Analyze index usage (PostgreSQL)
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Check index size
SELECT 
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public';

-- Explain query plan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Force index usage (MySQL)
SELECT * FROM users 
USE INDEX (idx_email)
WHERE email = 'test@example.com';

-- Index recommendations
-- Index columns used in:
-- - WHERE clauses
-- - JOIN conditions
-- - ORDER BY clauses
-- - Foreign keys

-- Example: Optimize this query
-- Before:
SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 'pending'
ORDER BY created_at DESC;

-- Create index:
CREATE INDEX idx_user_status_date 
ON orders(user_id, status, created_at DESC);