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
Resources PostgreSQL Indexes Documentation MySQL Indexes Documentation PostgreSQL Index Types