database-indexing-strategy
Design and implement database indexing strategies. Use when creating indexes, choosing index types, or optimizing index performance in PostgreSQL and MySQL.
Installation
Copy to your project
cp -r skills/database-indexing-strategy/ /your-project/.claude/skills/database-indexing-strategy/Database Indexing Strategy
Overview
Design comprehensive indexing strategies to improve query performance, reduce lock contention, and maintain data integrity. Covers index types, design patterns, and maintenance procedures.
When to Use
- Index creation and planning
- Query performance optimization through indexing
- Index type selection (B-tree, Hash, GiST, BRIN)
- Composite and partial index design
- Index maintenance and monitoring
- Storage optimization with indexes
- Full-text search index design
Index Types and Use Cases
PostgreSQL Index Types
B-tree Indexes (Default):
-- Standard equality and range queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
-- Composite indexes for multi-column queries
CREATE INDEX idx_orders_user_status
ON orders(user_id, status)
WHERE cancelled_at IS NULL;
Hash Indexes:
-- Exact match queries only
CREATE INDEX idx_product_sku USING hash ON products(sku);
-- Good for equality lookups on large text fields
CREATE INDEX idx_uuid_hash USING hash ON sessions(session_id);
BRIN Indexes (Block Range):
-- For large tables with monotonically increasing columns
CREATE INDEX idx_events_timestamp USING brin ON events(created_at)
WITH (pages_per_range = 128);
-- Excellent for time-series data
CREATE INDEX idx_logs_timestamp USING brin
ON application_logs(log_timestamp);
GiST & GIN Indexes:
-- GiST for spatial data and complex types
CREATE INDEX idx_locations_geom USING gist ON locations(geom);
-- GIN for JSONB and array columns
CREATE INDEX idx_products_metadata USING gin ON products(metadata);
CREATE INDEX idx_user_tags USING gin ON users(tags);
MySQL Index Types
B-tree Indexes:
-- Standard index for most queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);
-- Prefix indexes for large columns
CREATE INDEX idx_description_prefix
ON products(description(100));
FULLTEXT Indexes:
-- Full-text search on text columns
CREATE FULLTEXT INDEX idx_products_search
ON products(name, description);
-- Query using MATCH...AGAINST
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('laptop' IN BOOLEAN MODE);
Spatial Indexes:
-- For geographic data
CREATE SPATIAL INDEX idx_locations
ON locations(geom);
Index Design Patterns
Single Column Indexes
PostgreSQL:
-- Filtered index for active records only
CREATE INDEX idx_users_active
ON users(created_at)
WHERE deleted_at IS NULL;
-- Descending order for LIMIT queries
CREATE INDEX idx_posts_published DESC
ON posts(published_at DESC)
WHERE status = 'published';
MySQL:
-- Simple equality lookup
CREATE INDEX idx_users_verified ON users(email_verified);
-- Range queries on numeric columns
CREATE INDEX idx_products_price ON products(price);
Composite Indexes
PostgreSQL - Optimal Ordering:
-- Order: equality columns, then range, then sort
-- Query: WHERE user_id = X AND created_at > Y ORDER BY id
CREATE INDEX idx_optimal_composite
ON orders(user_id, created_at, id);
-- Covering index to eliminate table access
CREATE INDEX idx_covering_orders
ON orders(user_id, status, created_at)
INCLUDE (total, currency);
MySQL - Leftmost Prefix:
-- MySQL uses leftmost prefix matching
-- Can be used by: (user_id), (user_id, status), (user_id, status, created_at)
CREATE INDEX idx_users_complex
ON users(user_id, status, created_at);
-- For queries: user_id + status + created_at
SELECT * FROM orders
WHERE user_id = 1 AND status = 'completed' AND created_at > '2024-01-01';
Partial/Filtered Indexes
PostgreSQL:
-- Only index active products
CREATE INDEX idx_active_products
ON products(category_id)
WHERE active = true;
-- Reduce index size and improve performance
CREATE INDEX idx_not_cancelled_orders
ON orders(user_id, created_at)
WHERE status != 'cancelled';
-- Complex filter conditions
CREATE INDEX idx_vip_orders
ON orders(total DESC)
WHERE total > 10000 AND customer_type = 'vip';
Expression Indexes
PostgreSQL:
-- Index on computed values
CREATE INDEX idx_users_email_lower
ON users(LOWER(email));
-- Enable case-insensitive searches
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';
-- Date extraction indexes
CREATE INDEX idx_orders_year
ON orders(EXTRACT(YEAR FROM created_at));
Index Maintenance
PostgreSQL Index Analysis:
-- Check index size and usage
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as size,
idx_scan as scans,
idx_tup_read as tuples_read
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
-- Find unused indexes
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE 'pg_toast%';
-- Rebuild fragmented indexes
REINDEX INDEX idx_users_email;
MySQL Index Statistics:
-- Check index cardinality
SELECT object_schema, object_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema != 'mysql'
ORDER BY count_star DESC;
-- Update table statistics
ANALYZE TABLE users;
ANALYZE TABLE orders;
Concurrent Index Creation
PostgreSQL - Non-blocking Index Creation:
-- Create index without locking table (PostgreSQL 9.2+)
CREATE INDEX CONCURRENTLY idx_new_column
ON large_table(new_column);
-- Safe for production
REINDEX INDEX CONCURRENTLY idx_products_price;
MySQL - Concurrent Index Creation:
-- MySQL 8.0 supports ALGORITHM=INPLACE with LOCK=NONE
ALTER TABLE users ADD INDEX idx_created (created_at),
ALGORITHM=INPLACE, LOCK=NONE;
-- Check online DDL progress
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
Performance Monitoring
PostgreSQL - Index Performance:
-- Top 10 most scanned indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC LIMIT 10;
-- Indexes with high read/scan ratio
SELECT indexname, idx_scan, idx_tup_read,
CASE WHEN idx_scan = 0 THEN 0
ELSE ROUND(idx_tup_read::numeric / idx_scan, 2) END as efficiency
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY efficiency DESC;
MySQL - Index Statistics:
-- Show table index information
SHOW INDEX FROM products;
-- Check cardinality (distribution)
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'products'
ORDER BY SEQ_IN_INDEX;
Index Creation Checklist
- Identify slow queries with EXPLAIN/EXPLAIN ANALYZE
- Check filter columns, JOIN conditions, ORDER BY clauses
- Consider index order (equality → range → sort)
- Use partial indexes to reduce size on large tables
- Include columns for covering indexes
- Monitor index usage after creation
- Drop unused indexes to save space
- Rebuild fragmented indexes periodically
Common Mistakes
❌ Don’t create too many indexes (write performance impact) ❌ Don’t create indexes without testing first ❌ Don’t ignore index size and storage impact ❌ Don’t forget to update table statistics after bulk operations ❌ Don’t create duplicate indexes
✅ DO create indexes on foreign keys ✅ DO test index impact on INSERT/UPDATE performance ✅ DO use covering indexes for common queries ✅ DO drop unused indexes regularly ✅ DO monitor index fragmentation