database indexing performance postgresql mysql optimization

Database indexing strategies for performance

Maria Garcia Feb 2026

2 tabs

-- 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;

2 files · sql Explain with highlit

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.