Database Indexing & Query Optimization

Index Types

B-Tree Index

• Default for most databases (MySQL, PostgreSQL)
• Balanced tree structure
• Good for range queries and sorting

Hash Index

• O(1) lookup for equality
• Not suitable for range queries
• Fast point lookups

Full-Text Index

• Optimized for text search
• Language-specific analysis
• Used with text search queries

Spatial Index

• R-tree, Quadtree for geographic data
• Optimized for spatial queries

Composite Index

• Multiple columns in one index
• Column order matters (leftmost prefix)

Query Optimization Techniques

EXPLAIN Plans

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

Index Selection

1. Look for WHERE clause columns
2. Consider JOIN conditions
3. Evaluate sorting/grouping columns
4. Check cardinality (selectivity)

Avoid Common Mistakes

• Creating indexes on low-cardinality columns
• Creating unused indexes
• Over-indexing (write performance impact)
• Not analyzing index usage

Performance Tuning

1. Analyze queries - Use EXPLAIN
2. Identify bottlenecks - Query profiling
3. Test thoroughly - Before/after metrics
4. Monitor regularly - Track performance changes
5. Denormalize carefully - Balance read vs write
6. Archive old data - Keep active data small
7. Partition tables - Handle large datasets

Schema Design

• Normalization - Reduce redundancy
• Appropriate data types - Use INT not VARCHAR for IDs
• Foreign keys - Maintain referential integrity
• Constraints - Enforce data quality

Tools & Commands

CREATE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email;
ANALYZE;

EXPLAIN analyzer SELECT * FROM users WHERE email = 'test@example.com';
CREATE INDEX idx_email ON users(email);

References

• PostgreSQL Index Documentation
• MySQL Performance Tuning
• Database Query Optimization Principles
• Use the Index, Luke! (Free online book)