Database Optimization for a Growing Website

A website that feels fast when it launches often becomes noticeably slower as it grows. The code is the same, the server has the same capacity, but page load time creeps up from 500ms to 2 seconds. Usually, the database is the culprit.

Databases are optimized for correctness and consistency, not raw speed. A query that takes 50ms with 10,000 records might take 5 seconds with 10 million records. The query is identical; the data volume changed. Database optimization is about keeping queries fast as data grows.

Why Databases Slow Down

Databases slow down for three main reasons:

Slow queries. A query that scans every row in a table instead of using an index will get slower as the table grows. A query scanning 10 rows takes 1ms. A query scanning 10 million rows takes 10 seconds.

Missing indexes. An index is like a book's index—it lets you find information without reading every page. Without indexes, the database reads every row. With indexes, it finds data instantly.

Unbounded table growth. Over time, tables grow with data: customer records, orders, transactions, logs. If nothing cleans up old data, tables become massive and queries slow down.

Most slow sites have all three problems.

Understanding Database Indexes

An index is a data structure that speeds up lookups. Think of it like an index in a book: instead of reading every page to find references to "database," you look in the index, find page numbers instantly.

A query without an index:

SELECT * FROM users WHERE email = 'user@example.com';

The database scans every row in the users table looking for the email. With 1 million users, it scans 1 million rows. This takes time proportional to table size.

A query with an index:

Add an index on the email column:

CREATE INDEX idx_users_email ON users(email);

Now the same query doesn't scan every row. It uses the index to find the exact row instantly, regardless of table size.

How indexes help:

• Single-column indexes speed up lookups on that column
• Multi-column (composite) indexes speed up queries filtering on multiple columns
• Indexes slow down inserts/updates (the database must update the index)

The tradeoff is worth it for frequently queried columns.

Identifying Slow Queries

Most databases have query logging and analysis tools.

MySQL slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

This logs queries that take longer than 2 seconds. Review the log to find candidates for optimization:

mysqldumpslow /path/to/slow.log

PostgreSQL query analysis:

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';

This shows how the database executes the query and how many rows it scans.

Application-level monitoring:

Database monitoring tools (New Relic, Datadog, AWS RDS Performance Insights) show slow queries from your application's perspective with context about which page or API endpoint triggered them.

Start by logging slow queries. Usually, 5-10 queries account for 80% of database time. Fix those, and you get significant performance improvement.

Common Slow Query Patterns

Full table scans:

SELECT * FROM orders WHERE status = 'completed';

Without an index on status, this scans every row. Add an index:

CREATE INDEX idx_orders_status ON orders(status);

Now the database finds all completed orders instantly.

Missing joins:

SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'USA';

Without an index on orders.customer_id, the join scans every order. Add an index:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Sorting without indexes:

SELECT * FROM posts ORDER BY created_at LIMIT 10;

Without an index on created_at, the database scans every row and sorts (expensive). Add an index:

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

Now the database uses the index to find the 10 most recent posts instantly.

Complex filters:

SELECT * FROM products 
WHERE category = 'Electronics' 
AND price > 100 
AND rating > 4.0;

Add a composite index on the frequently-used filter columns:

CREATE INDEX idx_products_category_price_rating 
ON products(category, price, rating);

Query Optimization Techniques

Rewrite queries to use indexes. Sometimes a query is written in a way that prevents index use. Rewriting it allows indexes to work.

Bad:

WHERE YEAR(created_at) = 2025

Good:

WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

The first can't use an index on created_at. The second can.

Fetch only needed columns.

Bad:

SELECT * FROM orders;

Good:

SELECT id, total, created_at FROM orders;

Fetching only needed columns reduces data transfer and cache usage.

Paginate large result sets.

Bad:

SELECT * FROM users; (2 million rows)

Good:

SELECT * FROM users LIMIT 50 OFFSET 0; (50 rows)

Pagination ensures you fetch only what you need at a time.

Use database-specific optimizations. Different databases have different features:

• MySQL: use EXPLAIN to analyze query plans
• PostgreSQL: use EXPLAIN ANALYZE for detailed analysis
• MongoDB: create indexes on frequently-queried fields
• DynamoDB: choose partition keys wisely

Data Cleanup: Removing Growth

As data accumulates, old data takes up space and slows queries. Implement data retention policies.

Archive old data. Data older than 1-2 years might not need real-time access. Archive it to cold storage and query only recent data in the main database.

Delete unnecessary logs. Application logs, error logs, and analytics data often accumulate endlessly. Set up automated deletion:

DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

This keeps logs from the last 90 days and deletes older entries.

Partition large tables. For very large tables (100+ million rows), partition by date or range:

CREATE TABLE orders_2025_q1 LIKE orders;
CREATE TABLE orders_2025_q2 LIKE orders;

Queries on specific quarters are faster because they scan only the relevant partition.

Monitor table size. Know how big your tables are and watch for unusual growth.

SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY size_mb DESC;

This shows which tables consume the most space.

Connection Pooling

If your application creates new database connections for every query, overhead accumulates. Connection pooling reuses connections:

Without pooling:

1. Create connection (slow)
2. Execute query (fast)
3. Close connection (overhead)
4. Create connection (slow)
5. Execute query (fast)

With pooling:

1. Get connection from pool (instant)
2. Execute query (fast)
3. Return connection to pool (instant)

Many applications already use connection pooling, but verify it's configured:

• Django: use persistent connections with CONN_MAX_AGE
• Rails: configure pool size in database.yml
• Node: use connection pooling libraries like mysql2 or pg with pool settings

Common Optimization Mistakes

Adding indexes randomly. Indexes speed up SELECT queries but slow down INSERT/UPDATE. Index only columns that are frequently queried, not everything.

Not monitoring indexes. Unused indexes waste space and slow inserts. Review indexes periodically and remove unused ones.

Optimizing the wrong query. A query that takes 100ms is faster than one taking 10ms, so optimize the slow one. Use query logs to find the actual bottleneck.

Ignoring connection overhead. If you're creating new database connections for every request, connection overhead is substantial. Use pooling.

Not testing optimization. Always test on a copy of your production database with production-scale data. An index that helps with 10,000 rows might not help with 10 million rows.

FAQ

How many indexes should a table have?

Usually 3-5 indexes per table, depending on query patterns. Too many indexes slow down inserts/updates.

Should I index every column?

No. Index columns that are frequently queried or filtered on. Don't index columns that are rarely used in queries.

When should I use database partitioning?

When a table grows to 100+ million rows and queries on specific ranges (date ranges, regions, etc.) are common.

Should I use a separate read replica?

Yes, if your site has mostly reads (like a blog) and you're hitting database limits. A read replica handles SELECT queries while the primary handles writes.

Does caching replace database optimization?

No. Caching reduces database load, but you still need optimization for cache misses and write queries.

How often should I rebuild indexes?

Indexes fragment over time and need rebuilding every few months or when you notice performance degradation:

OPTIMIZE TABLE table_name; -- MySQL
REINDEX INDEX index_name; -- PostgreSQL

The Optimization Process

Database optimization isn't a one-time task. As your site grows and usage patterns change, new bottlenecks emerge. The process is:

1. Monitor: Track database performance and identify slow queries
2. Analyze: Understand why queries are slow
3. Optimize: Add indexes, rewrite queries, clean up data
4. Test: Verify optimization helps without breaking other queries
5. Repeat: Monitor continuously and optimize as needed

A well-optimized database is the difference between a site that scales smoothly and one that crawls to a halt as it grows.