Database Indexing for Performance
Master database indexing strategies to achieve maximum query performance. Learn B-tree, hash, composite indexes, and optimization techniques for MySQL, PostgreSQL, and MongoDB.
Published: September 25, 2024 | Reading time: 22 minutes
📊 Indexing Performance Impact
Without Index: Full table scan - O(n) complexity
With B-Tree Index: Logarithmic search - O(log n) complexity
Performance Gain: 100x to 1000x faster queries on large tables
Performance Comparison
Types of Database Indexes
🌳 B-Tree Indexes (Most Common)
B-tree indexes are the default index type in most databases. They provide excellent performance for equality and range queries.
-- Create B-tree index
CREATE INDEX idx_user_email ON users(email);
-- Query using index
SELECT * FROM users WHERE email = 'user@example.com';
-- Range query using index
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
🔗 Hash Indexes
Hash indexes provide O(1) lookup time for equality queries but don't support range queries or sorting.
-- Create hash index
CREATE INDEX idx_user_id_hash ON users USING hash(id);
-- Equality query (very fast)
SELECT * FROM users WHERE id = 12345;
-- Range query (cannot use hash index)
SELECT * FROM users WHERE id > 1000; -- Will use sequential scan
🔠Composite Indexes
Composite indexes use multiple columns and are most effective when queries use the leftmost columns.
-- Create composite index
CREATE INDEX idx_user_status_created ON users(status, created_at);
-- ✅ Uses index (leftmost column)
SELECT * FROM users WHERE status = 'active';
-- ✅ Uses index (both columns)
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';
-- ⌠Cannot use index efficiently (skips leftmost column)
SELECT * FROM users WHERE created_at > '2024-01-01';
📠Partial Indexes
Partial indexes only index rows that meet a specific condition, reducing index size and improving performance.
-- Create partial index for active users only
CREATE INDEX idx_active_users_email ON users(email)
WHERE status = 'active';
-- ✅ Uses partial index
SELECT * FROM users WHERE status = 'active' AND email = 'user@example.com';
-- ⌠Cannot use partial index
SELECT * FROM users WHERE status = 'inactive' AND email = 'user@example.com';
Index Strategy by Database
🬠MySQL Indexing
MySQL uses B-tree indexes by default with InnoDB storage engine.
-- Primary key (clustered index)
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP
);
-- Secondary indexes
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created ON users(created_at);
-- Composite index
CREATE INDEX idx_status_created ON users(status, created_at);
😠PostgreSQL Indexing
PostgreSQL offers multiple index types including B-tree, Hash, GIN, and GiST.
-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
-- Hash index for equality
CREATE INDEX idx_users_id_hash ON users USING hash(id);
-- GIN index for JSON/arrays
CREATE INDEX idx_users_tags_gin ON users USING gin(tags);
-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
🃠MongoDB Indexing
MongoDB supports single field, compound, multikey, and text indexes.
// Single field index
db.users.createIndex({ email: 1 })
// Compound index
db.users.createIndex({ status: 1, created_at: -1 })
// Text index for search
db.users.createIndex({ name: "text", bio: "text" })
// Partial index
db.users.createIndex(
{ email: 1 },
{ partialFilterExpression: { status: "active" } }
)
Query Analysis and Optimization
🔠Analyzing Query Performance
Use EXPLAIN (MySQL/PostgreSQL) or explain() (MongoDB) to analyze query execution plans and identify missing indexes.
-- Analyze query performance
EXPLAIN SELECT * FROM users
WHERE email = 'user@example.com'
AND status = 'active';
-- Results show:
-- type: ref (good - uses index)
-- key: idx_email (index used)
-- rows: 1 (efficient)
-- Without index:
-- type: ALL (bad - full table scan)
-- key: NULL (no index)
-- rows: 1000000 (inefficient)
Index Monitoring and Maintenance
📈 Index Usage Monitoring
Monitor index usage to identify unused indexes and optimize your database performance.
| Database |
Command |
Purpose |
| MySQL |
SHOW INDEX FROM table_name |
View index statistics |
| PostgreSQL |
SELECT * FROM pg_stat_user_indexes |
Monitor index usage |
| MongoDB |
db.collection.getIndexes() |
List all indexes |
| MySQL |
SELECT * FROM information_schema.INDEX_STATISTICS |
Index usage statistics |
Common Indexing Problems
âš ï¸ Index Troubleshooting
1
Too Many Indexes
Each index slows down INSERT/UPDATE/DELETE operations
Solution: Remove unused indexes, use composite indexes
2
Wrong Column Order
Composite indexes are only effective when used from left to right
Solution: Order columns by selectivity (most selective first)
3
Missing Indexes
Queries performing full table scans
Solution: Analyze slow query log, add appropriate indexes
4
Index Fragmentation
Indexes become fragmented over time, reducing performance
Solution: Rebuild indexes periodically (MySQL: OPTIMIZE TABLE)
Index Best Practices
✅ Index Best Practices
1
Index Selectivity
Create indexes on columns with high selectivity (many unique values)
Example: Email addresses, user IDs, timestamps
2
Composite Index Order
Order columns from most selective to least selective
Example: CREATE INDEX idx_user_status_date ON users(status, created_at)
3
Covering Indexes
Include all columns needed by the query in the index
Example: CREATE INDEX idx_user_covering ON users(email, name, status)
4
Regular Maintenance
Monitor index usage and remove unused indexes
Tools: MySQL slow query log, PostgreSQL pg_stat_statements
Index Type Comparison
Index Type Performance Comparison
| Index Type |
Equality Queries |
Range Queries |
Sorting |
Storage Overhead |
Best Use Case |
| B-Tree |
Excellent |
Excellent |
Excellent |
Medium |
General purpose, most queries |
| Hash |
Excellent |
Poor |
Poor |
Low |
Exact lookups only |
| Composite |
Excellent |
Good |
Good |
High |
Multi-column queries |
| Partial |
Excellent |
Good |
Good |
Low |
Filtered data sets |
Real-World Indexing Examples
E-commerce Product Search
-- Products table with search optimization
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
category_id INT,
price DECIMAL(10,2),
status ENUM('active', 'inactive'),
created_at TIMESTAMP,
updated_at TIMESTAMP
);
-- Composite index for category + status + price
CREATE INDEX idx_products_category_status_price
ON products(category_id, status, price);
-- Text search index (if using full-text search)
CREATE FULLTEXT INDEX idx_products_name_search
ON products(name);
-- Query using optimized indexes
SELECT * FROM products
WHERE category_id = 1
AND status = 'active'
AND price BETWEEN 10 AND 100
ORDER BY price ASC;
User Activity Analytics
-- User activities table
CREATE TABLE user_activities (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
activity_type VARCHAR(50),
created_at TIMESTAMP,
metadata JSON
);
-- Composite index for user + activity + time
CREATE INDEX idx_activities_user_type_time
ON user_activities(user_id, activity_type, created_at);
-- Partial index for recent activities only
CREATE INDEX idx_recent_activities
ON user_activities(user_id, created_at)
WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);
-- Analytics query using indexes
SELECT
user_id,
activity_type,
COUNT(*) as activity_count,
DATE(created_at) as activity_date
FROM user_activities
WHERE user_id = 12345
AND created_at >= '2024-01-01'
GROUP BY user_id, activity_type, DATE(created_at)
ORDER BY activity_date DESC;
Index Maintenance Scripts
-- MySQL: Find unused indexes
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
s.INDEX_NAME,
s.CARDINALITY
FROM information_schema.TABLES t
LEFT JOIN information_schema.STATISTICS s
ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'your_database'
AND s.INDEX_NAME != 'PRIMARY'
AND s.CARDINALITY = 0;
-- PostgreSQL: Index usage statistics
SELECT
schemaname,
tablename,
indexname,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_tup_read = 0
AND indexname NOT LIKE '%_pkey';
-- MySQL: Optimize tables (rebuild indexes)
OPTIMIZE TABLE users, products, orders;
Summary
Effective database indexing is crucial for optimal performance:
- B-tree indexes are the most versatile and commonly used
- Composite indexes should order columns by selectivity
- Monitor index usage to identify unused indexes
- Regular maintenance prevents fragmentation
- Covering indexes can eliminate table lookups
Need Index Optimization?
Our database performance experts can analyze your queries and design optimal indexing strategies for maximum performance.
Get Index Help