Database Optimization: SQL Performance and Indexing

Why Database Performance Matters

Slow database queries increase application response time. Queries longer than 100ms negatively affect user experience.

Database bottleneck can affect 80% of application performance. Database is usually the biggest bottleneck rather than CPU or memory.

Optimization reduces hardware costs. More efficient queries require fewer server resources.

Indexing Strategies

B-Tree index: Most common index type. Effective for equality (=) and range (<, >, BETWEEN) queries. Primary keys and foreign keys are automatically indexed.

Composite index: Index containing multiple columns. Determine column order based on query order. The first column in WHERE clause should be most selective.

Covering index: Index containing all columns used in query. No table access needed, only index is read.

Partial index: Index for rows meeting specific condition. Like WHERE is_active = true. Reduces index size.

Index maintenance: Remove unused indexes. Each index slows down INSERT/UPDATE/DELETE operations. Check index usage with EXPLAIN.

Query Optimization

EXPLAIN / EXPLAIN ANALYZE: Analyze query execution plan. Target Index Scan instead of Seq Scan (full table scan).

N+1 problem: Fetch related data in a single query (JOIN) instead of making separate query for each record. Use eager loading in ORMs.

Don't use SELECT *: Select only necessary columns. This reduces network traffic and memory usage.

JOIN optimization: Avoid unnecessary JOINs. JOIN smaller tables first. Use INNER JOIN instead of LEFT JOIN (if possible).

Subquery vs JOIN: Correlated subqueries are slow. Use JOIN or CTE (Common Table Expression) when possible.

Caching and Connection Pooling

Query cache: Cache frequently used query results. Do application-level caching with Redis or Memcached.

Connection pooling: Reuse database connections. Opening a new connection for each request is expensive. Use pgBouncer (PostgreSQL) or HikariCP (Java).

Read replicas: Route read operations to replica servers. Keep write operations on primary server. This distributes read load.

Materialized views: Create materialized view for complex aggregation queries. Refresh periodically.

Database Design

Normalization: Reduce data repetition (1NF, 2NF, 3NF). However, over-normalization increases JOIN count and reduces performance.

Denormalization: Denormalization improves performance in read-heavy systems (analytics, reporting). Trade-off: data consistency vs performance.

Partitioning: Partition large tables (millions of rows). Range partitioning (date-based) is the most common method.

Data types: Use correct data types. INT instead of VARCHAR, VARCHAR (appropriate size) instead of TEXT. Smaller data types take less space and process faster.

Monitoring and Profiling

Slow query log: Log slow queries. PostgreSQL: log_min_duration_statement = 100. MySQL: slow_query_log = 1.

Performance Schema (MySQL) / pg_stat_statements (PostgreSQL): Collect query statistics. Identify queries that take the most time.

APM tools: Monitor database performance with Datadog, New Relic, or Dynatrace. Track query times and error rates.

Index usage: Check index usage with pg_stat_user_indexes (PostgreSQL). Remove unused indexes.