SQL Indexing Guide: Complete Database Performance Strategy
Learn 5 essential indexing strategies to speed up queries, from single-column indexes to covering indexes and index maintenance.
Why Database Indexing Is Critical for Performance
Database indexes are the single most important factor in query performance. Without proper indexes, databases perform full table scans, reading every row to find matching records. On a table with millions of rows, this means the difference between millisecond and minute-long queries.
Indexes work like a book's index: instead of reading every page to find a topic, you look it up in the index and jump directly to the relevant pages. Database indexes use similar data structures (B-trees, hash tables) to enable fast lookups. The challenge is knowing which columns to index and understanding the trade-offs.
This guide covers 5 fundamental indexing strategies that address the most common performance bottlenecks. You'll learn when to use single-column indexes, how to design composite indexes, when covering indexes provide benefits, and how to maintain indexes over time.
Common Indexing Mistakes
Indexing Every Column
Adding indexes on every column seems like a safe strategy, but it slows down INSERT, UPDATE, and DELETE operations significantly. Each index must be maintained when data changes. Focus on columns actually used in WHERE, JOIN, and ORDER BY clauses based on your query patterns.
Wrong Column Order in Composite Indexes
The order of columns in composite indexes matters due to the leftmost prefix rule. Placing a low-selectivity column first reduces index effectiveness. Always put the most selective column first, followed by columns used in range queries. Test with EXPLAIN to verify the index is being used.
Ignoring Index Maintenance
Indexes fragment over time and statistics become stale, reducing their effectiveness. Regular ANALYZE updates statistics for the query planner. Rebuild fragmented indexes with REINDEX. Monitor index usage statistics to identify and remove unused indexes that waste space and slow writes.
Not Testing Index Impact
Adding indexes without measuring their impact is guesswork. Use EXPLAIN ANALYZE to verify the database uses your index and measure query time improvements. Check index size and write operation performance. An index that doesn't improve query performance or slows writes too much should be removed.
Essential SQL Indexing Strategies
These five strategies cover the most important indexing techniques for database performance.
Single-Column Indexes
Create indexes on individual columns used in WHERE clauses and JOIN conditions
Example:
-- Create index on frequently queried column
CREATE INDEX idx_customers_email ON customers(email);
-- Index on foreign key for faster joins
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Index on columns used in WHERE clauses
CREATE INDEX idx_products_category ON products(category);
-- Check if index is being used
EXPLAIN SELECT * FROM customers WHERE email = 'user@example.com';When to Use:
Single-column indexes are the foundation of database performance. Create them on columns frequently used in WHERE clauses, JOIN conditions, and foreign keys. They dramatically speed up lookups but add overhead to INSERT, UPDATE, and DELETE operations. Focus on high-cardinality columns (many unique values) for maximum benefit.
Composite Indexes (Multi-Column)
Combine multiple columns in a single index for complex queries
Example:
-- Composite index for filtering and sorting
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
-- Index supports queries filtering by customer_id
SELECT * FROM orders WHERE customer_id = 123;
-- Also supports queries filtering by both columns
SELECT * FROM orders
WHERE customer_id = 123 AND order_date >= '2025-01-01';
-- Column order matters: leftmost prefix rule
-- This index does NOT help: WHERE order_date >= '2025-01-01'When to Use:
Composite indexes combine multiple columns into one index. The column order matters due to the leftmost prefix rule: queries can use the index if they filter on the leftmost columns. Place the most selective (highest cardinality) column first, followed by columns used in range queries. Ideal for queries with multiple WHERE conditions or ORDER BY clauses.
Covering Indexes
Include all columns needed by a query to avoid table lookups
Example:
-- Covering index includes all columns in SELECT
CREATE INDEX idx_orders_covering
ON orders(customer_id, order_date, total_amount, status);
-- This query uses ONLY the index (no table access)
SELECT customer_id, order_date, total_amount, status
FROM orders
WHERE customer_id = 123 AND order_date >= '2025-01-01';
-- PostgreSQL: INCLUDE clause for non-key columns
CREATE INDEX idx_orders_covering_pg
ON orders(customer_id, order_date)
INCLUDE (total_amount, status);When to Use:
Covering indexes contain all columns referenced in a query (SELECT, WHERE, ORDER BY). This allows the database to satisfy queries entirely from the index without accessing the table, resulting in index-only scans. They use more disk space but provide significant performance gains for frequently-run queries. Use EXPLAIN to verify index-only scans.
Partial Indexes (Filtered)
Index only a subset of rows based on a WHERE condition
Example:
-- PostgreSQL: Index only active orders
CREATE INDEX idx_orders_active
ON orders(customer_id, order_date)
WHERE status = 'active';
-- MySQL: Use filtered index with specific condition
CREATE INDEX idx_orders_recent
ON orders(customer_id, order_date, total_amount)
WHERE order_date >= '2025-01-01';
-- Index is used only for matching queries
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'active';When to Use:
Partial indexes index only rows matching a specific condition, reducing index size and improving performance for targeted queries. They are ideal when you frequently query a subset of data (e.g., active records, recent data, specific statuses). Smaller indexes mean faster lookups and less storage. Note: MySQL support varies by version.
Index Maintenance and Analysis
Monitor, rebuild, and optimize indexes for ongoing performance
Example:
-- PostgreSQL: Analyze table and update statistics
ANALYZE orders;
-- Check index usage statistics
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;
-- Rebuild fragmented index
REINDEX INDEX idx_orders_customer_id;
-- MySQL: Analyze and optimize table
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;
-- Show index cardinality
SHOW INDEX FROM orders;When to Use:
Indexes degrade over time due to updates and fragmentation. Regular maintenance includes analyzing tables to update statistics (helps query planner), monitoring index usage to identify unused indexes, and rebuilding fragmented indexes. Remove unused indexes to reduce write overhead. Schedule maintenance during low-traffic periods. Use database-specific tools to track index bloat.
Your First Steps with Database Indexing
Step 1: Identify Slow Queries
Enable slow query logging and identify queries taking longer than acceptable thresholds. Use EXPLAIN ANALYZE to see execution plans and identify full table scans.
-- Find queries scanning entire tables
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;Step 2: Index Foreign Keys and WHERE Columns
Start with the basics: create indexes on all foreign keys and columns frequently used in WHERE clauses. These provide the biggest performance wins with minimal complexity.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);Step 3: Monitor and Optimize
After adding indexes, verify they are being used with EXPLAIN. Monitor index usage statistics to identify unused indexes. Schedule regular ANALYZE and REINDEX operations.
-- Verify index usage
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- Check index statistics
ANALYZE orders;Frequently Asked Questions About SQL Indexing
When should I add an index to a table?
Add indexes on columns frequently used in WHERE clauses, JOIN conditions, ORDER BY, and GROUP BY. Prioritize foreign keys and columns with high cardinality (many unique values). Avoid indexing columns with low cardinality (few unique values like boolean fields) unless they are very selective in combination with other columns. Always test with real data and query patterns.
How many indexes is too many?
There is no fixed limit, but each index adds overhead to INSERT, UPDATE, and DELETE operations. Start with indexes on foreign keys and frequently queried columns. Monitor query performance and add indexes as needed. Remove unused indexes by checking index usage statistics. A table with 5-10 indexes is common, but this varies by workload.
What is the leftmost prefix rule for composite indexes?
The leftmost prefix rule means a composite index can only be used by queries that filter on the leftmost column(s). For index (A, B, C), queries can use WHERE A, WHERE A AND B, or WHERE A AND B AND C. But WHERE B or WHERE C alone cannot use the index. Order columns by selectivity: most selective first, then columns used in range queries.
Should I index every foreign key?
Yes, in most cases. Foreign keys are frequently used in JOINs, and indexing them dramatically speeds up join operations. Without an index, the database performs full table scans when joining. The only exception might be very small lookup tables (a few hundred rows) where scans are fast anyway.
What is the difference between a clustered and non-clustered index?
A clustered index determines the physical order of data in the table (only one per table). Non-clustered indexes are separate structures that point to data rows. In MySQL InnoDB, the primary key is the clustered index. In PostgreSQL, all indexes are non-clustered. Clustered indexes are faster for range scans on the indexed column.
How do I find unused indexes?
Use database statistics to identify unused indexes. In PostgreSQL, query pg_stat_user_indexes for idx_scan = 0. In MySQL, check INFORMATION_SCHEMA.STATISTICS. Remove unused indexes to reduce write overhead. Be cautious: some indexes may be used infrequently but are critical for specific queries or reports.
Do indexes slow down writes?
Yes, indexes add overhead to INSERT, UPDATE, and DELETE operations because the database must update the index along with the table. This is the trade-off: faster reads but slower writes. The impact is usually acceptable because reads vastly outnumber writes in most applications. Index strategically on columns that provide the most query benefit.
What is index cardinality and why does it matter?
Cardinality is the number of unique values in a column. High cardinality (many unique values, like email addresses) makes good index candidates. Low cardinality (few unique values, like boolean or status fields) provides less benefit unless combined with other columns in a composite index. The query planner uses cardinality to decide whether to use an index.
Go Deeper with the Complete SQL Crash Course
This guide covers database indexing fundamentals. The complete book includes advanced indexing strategies, query optimization, and comprehensive performance tuning techniques.
What You'll Get:
- ✓350+ practical SQL examples and exercises covering all skill levels
- ✓140+ SQL interview questions with detailed solutions
- ✓Complete e-commerce database for hands-on practice
- ✓SQL code library with 380+ SQL statements ready to use
- ✓2 bonus SQL cheat sheets: Glossary and Dialect Differences
Trusted by developers worldwide
4.8/5 rating - Bestselling SQL book