-- Create basic index
CREATE INDEX idx_users_email ON users(email);
-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at);
-- This index helps queries like:
-- WHERE user_id = ? AND status = ? AND created_at > ?
-- WHERE user_id = ? AND status = ?
-- WHERE user_id = ?
-- But NOT: WHERE status = ? (doesn't use index efficiently)
-- Partial index (PostgreSQL): Index subset of rows
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- Covering index: Include extra columns
CREATE INDEX idx_users_email_covering
ON users(email)
INCLUDE (name, created_at); -- PostgreSQL 11+
-- Or in MySQL:
CREATE INDEX idx_users_email_name
ON users(email, name, created_at);
-- Expression index
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));
-- Helps with case-insensitive searches:
-- WHERE LOWER(email) = 'user@example.com'
-- Descending index for ORDER BY DESC
CREATE INDEX idx_posts_created_desc
ON posts(created_at DESC);
-- Check index usage
SELECT
schemaname,
tablename,
indexname,
idx_scan as index_scans,
idx_tup_read as tuples_read,
idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Full-text search index (PostgreSQL)
CREATE INDEX idx_articles_search
ON articles
USING GIN(to_tsvector('english', title || ' ' || content));
-- Query using full-text index:
SELECT title, content
FROM articles
WHERE to_tsvector('english', title || ' ' || content)
@@ to_tsquery('english', 'database & performance');
-- GiST index for geometric data
CREATE INDEX idx_locations_point
ON locations
USING GIST(coordinates);
-- JSON index (PostgreSQL)
CREATE INDEX idx_users_metadata
ON users
USING GIN(metadata jsonb_path_ops);
-- Query JSON with index:
SELECT * FROM users
WHERE metadata @> '{"premium": true}';
-- Concurrent index creation (no table lock)
CREATE INDEX CONCURRENTLY idx_large_table_column
ON large_table(column_name);
-- Drop unused indexes
DROP INDEX idx_old_unused_index;
-- Reindex for maintenance
REINDEX INDEX idx_users_email;
REINDEX TABLE users;
-- Find missing indexes (PostgreSQL)
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;
-- Find duplicate/redundant indexes
SELECT
indrelid::regclass AS table_name,
array_agg(indexrelid::regclass) AS indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING COUNT(*) > 1;
-- Index bloat check
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
AND indexrelid::regclass::text NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
Indexes dramatically speed up queries but slow down writes. B-tree indexes handle equality and range queries—default for most databases. I create indexes on foreign keys, frequently queried columns, and WHERE/ORDER BY clauses. Composite indexes order matters—most selective column first. Partial indexes filter rows, reducing index size. Covering indexes include all query columns, avoiding table lookups. UNIQUE indexes enforce constraints while providing lookup speed. Full-text indexes enable text search. Analyze query plans with EXPLAIN to identify missing indexes. Over-indexing wastes space and slows writes. Regular REINDEX maintains performance. Understanding index types—B-tree, Hash, GiST, GIN—optimizes different workloads.
