Optimizing Database Queries: Techniques and Best Practices

Most performance problems aren't in your application code. They're in your database queries.

I've seen a single unoptimized query bring down a production system serving 50,000 users. A missing index turned a 5ms lookup into a 12-second full table scan. Multiply that by concurrent requests and the database locks up.

Here's how to find and fix slow queries.

1. Indexing

This is the single highest-impact optimization. Without an index, the database reads every row in the table to find your data. With an index, it jumps straight to the matching rows.

CREATE INDEX idx_last_name ON employees (last_name);

CREATE INDEX idx_status_created ON orders (status, created_at);

• B-tree indexes handle range queries and equality checks. They're the default and cover most cases.
• Hash indexes are faster for exact-match lookups but can't handle ranges.
• Full-text indexes enable efficient text search across large documents.

Use EXPLAIN ANALYZE to verify your indexes are being used. A query plan showing "Seq Scan" means the database is ignoring your index.

2. Query Tuning

Small changes in how you write queries can have massive performance impacts.

Select only what you need. SELECT * pulls every column, including the ones you don't use. It wastes I/O, memory, and network bandwidth.

-- Slow: fetches everything
SELECT * FROM orders WHERE user_id = 42;

-- Fast: fetches only what you render
SELECT id, status, total FROM orders WHERE user_id = 42;

Avoid N+1 queries. If you fetch 100 users and then run a separate query for each user's orders, that's 101 queries. Use a JOIN or a batched IN clause instead.

Push filtering into the database. Don't fetch 10,000 rows and filter in application code. Let the database do it -- that's what it's optimized for.

3. Caching

If the same query runs thousands of times per minute and the data rarely changes, cache the result.

• Application-level cache: Store results in Redis or Memcached. Check the cache before hitting the database.
• Query cache: Some databases (MySQL) have built-in query caches, though they've been removed in MySQL 8.0 due to scalability issues.
• Materialized views: Precompute expensive aggregations and store the results. Refresh periodically.

The trade-off: cache invalidation is one of the hardest problems in computer science. Stale caches cause subtle bugs. Start simple -- cache things that change infrequently.

4. Denormalization

Normalized databases minimize redundancy but require JOINs. JOINs across large tables are expensive.

If a query joins five tables every time the dashboard loads, consider storing the combined result in a single table. You trade write complexity for read speed.

-- Instead of joining users, orders, and products on every dashboard load
CREATE TABLE dashboard_summary AS
SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

Refresh it on a schedule or via triggers. Accept that it might be slightly stale.

5. Pagination

Never return unbounded result sets. A query with no LIMIT on a table with millions of rows will consume all available memory.

-- Offset-based (simple but slow for large offsets)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 1000;

-- Cursor-based (fast regardless of page depth)
SELECT * FROM products WHERE id > 1000 ORDER BY id LIMIT 20;

Cursor-based pagination outperforms offset-based pagination at scale because it doesn't skip rows.

The Approach

Don't optimize blindly. Measure first. Enable slow query logging, identify your worst offenders, and fix them one at a time. A single bad query often accounts for most of your database load.