Database Query Optimization — The Complete Guide

Why Database Optimization Matters for Frontend Developers

Even if you primarily work on the frontend, your application's performance is often bottlenecked by how data is fetched. A React component that renders in 5ms is useless if the API call behind it takes 3 seconds. Understanding database query patterns helps you design better APIs, write faster data-fetching code, and collaborate effectively with backend engineers.

User clicks "Products" button
     |
     v
Frontend: fetch('/api/products')        ~5ms
     |
     v
Network: request travels to server      ~50ms
     |
     v
Server: parse request, auth check       ~10ms
     |
     v
Database: run query                     ~20ms - 5000ms  <-- THIS IS THE BOTTLENECK
     |
     v
Server: serialize response              ~5ms
     |
     v
Network: response travels back          ~50ms
     |
     v
Frontend: parse JSON, render            ~20ms

Total: 160ms (fast query) vs 5140ms (slow query)

The N+1 Query Problem

The N+1 problem is the most common database performance killer. It happens when you fetch a list of items, then make a separate query for each item's related data.

The Problem

// API route: GET /api/posts-with-authors
// This is the N+1 problem

// Query 1: Get all posts
const posts = await db.query('SELECT * FROM posts LIMIT 20');
// Returns 20 posts

// Queries 2-21: Get each post's author (one query per post!)
for (const post of posts) {
  post.author = await db.query(
    'SELECT * FROM users WHERE id = ?',
    [post.author_id]
  );
}

// Total: 1 + 20 = 21 queries
// If posts LIMIT 100 -> 101 queries!
// If posts LIMIT 1000 -> 1001 queries!

N+1 Query Pattern:
Query 1:  SELECT * FROM posts                     20ms
Query 2:  SELECT * FROM users WHERE id = 1         5ms
Query 3:  SELECT * FROM users WHERE id = 2         5ms
Query 4:  SELECT * FROM users WHERE id = 3         5ms
...
Query 21: SELECT * FROM users WHERE id = 20        5ms
                                          Total: 120ms

With JOIN (single query):
Query 1:  SELECT posts.*, users.name, users.avatar
          FROM posts
          JOIN users ON posts.author_id = users.id
          LIMIT 20                                 25ms
                                          Total:  25ms

That is a 5x improvement, and the gap widens with more records.

Fixing N+1 with JOIN

// Good: Single query with JOIN
const posts = await db.query(`
  SELECT
    posts.id,
    posts.title,
    posts.content,
    posts.created_at,
    users.id AS author_id,
    users.name AS author_name,
    users.avatar AS author_avatar
  FROM posts
  JOIN users ON posts.author_id = users.id
  ORDER BY posts.created_at DESC
  LIMIT 20
`);

// Transform flat results into nested objects
const result = posts.map(row => ({
  id: row.id,
  title: row.title,
  content: row.content,
  createdAt: row.created_at,
  author: {
    id: row.author_id,
    name: row.author_name,
    avatar: row.author_avatar,
  },
}));

Fixing N+1 with Batch Loading (DataLoader Pattern)

// When JOINs are impractical (e.g., GraphQL resolvers), use batch loading

class DataLoader {
  constructor(batchFn) {
    this.batchFn = batchFn;
    this.cache = new Map();
    this.queue = [];
    this.scheduled = false;
  }

  async load(key) {
    if (this.cache.has(key)) {
      return this.cache.get(key);
    }

    return new Promise((resolve) => {
      this.queue.push({ key, resolve });

      if (!this.scheduled) {
        this.scheduled = true;
        // Batch all loads in the same tick
        process.nextTick(() => this.dispatch());
      }
    });
  }

  async dispatch() {
    const batch = this.queue;
    this.queue = [];
    this.scheduled = false;

    const keys = batch.map(({ key }) => key);
    const results = await this.batchFn(keys);

    batch.forEach(({ key, resolve }, i) => {
      this.cache.set(key, results[i]);
      resolve(results[i]);
    });
  }
}

// Usage
const userLoader = new DataLoader(async (userIds) => {
  // Single query for ALL requested users
  const users = await db.query(
    'SELECT * FROM users WHERE id IN (?)',
    [userIds]
  );

  // Return in the same order as requested IDs
  const userMap = new Map(users.map(u => [u.id, u]));
  return userIds.map(id => userMap.get(id));
});

// These all batch into a single query:
const author1 = await userLoader.load(1);
const author2 = await userLoader.load(2);
const author3 = await userLoader.load(3);
// Actual query: SELECT * FROM users WHERE id IN (1, 2, 3)

Detecting N+1 Queries

// Middleware to detect N+1 queries in development
let queryCount = 0;
let queryLog = [];

function trackQuery(query) {
  queryCount++;
  queryLog.push({
    sql: query.sql,
    duration: query.duration,
    timestamp: Date.now(),
  });

  // Alert if too many queries per request
  if (queryCount > 10) {
    console.warn(
      `WARNING: ${queryCount} queries in this request. Possible N+1 problem.`
    );
    console.warn('Queries:', queryLog.map(q => q.sql));
  }
}

// Reset per request
function queryMiddleware(req, res, next) {
  queryCount = 0;
  queryLog = [];
  next();
}

Indexing Strategies

Indexes are the most impactful database optimization. Without them, the database scans every row to find matching records. With the right index, it jumps directly to the relevant rows.

How Indexes Work

Without index (full table scan):
Table: 1,000,000 rows
Query: SELECT * FROM users WHERE email = '[email protected]'
Process: Scan ALL 1,000,000 rows to find match
Time: ~500ms

With index on email column:
Same table, same query
Process: B-tree lookup, ~3-4 level traversal
Time: ~1ms

That is a 500x improvement.

B-Tree Index Structure:

              [M]
             /   \
          [G]     [S]
         / \     / \
       [C] [J] [O] [W]
       /\  /\  /\  /\
      Data leaves (pointers to actual rows)

Lookup: O(log n) — ~20 levels for 1 billion rows
Scan:   O(n) — must check every row

Common Index Types

+-------------------+----------------------------+----------------------------+
| Index Type        | Use Case                   | Example                    |
+-------------------+----------------------------+----------------------------+
| Single Column     | Filter/sort on one column  | INDEX (email)              |
| Composite         | Filter on multiple columns | INDEX (category, status)   |
| Unique            | Enforce uniqueness         | UNIQUE INDEX (email)       |
| Partial/Filtered  | Index subset of rows       | WHERE status = 'active'    |
| Full-text         | Text search                | FULLTEXT INDEX (content)   |
+-------------------+----------------------------+----------------------------+

Which Columns to Index

// Index columns that appear in:
// 1. WHERE clauses
// 2. JOIN conditions
// 3. ORDER BY clauses
// 4. GROUP BY clauses

// Example: An e-commerce products table
// Common queries and their index needs:

// Query: Products by category
// SELECT * FROM products WHERE category_id = 5 ORDER BY created_at DESC
// Index: (category_id, created_at DESC)

// Query: Search by name
// SELECT * FROM products WHERE name LIKE 'iPhone%'
// Index: (name) — only works for prefix searches (LIKE 'x%')

// Query: Active products by price range
// SELECT * FROM products WHERE status = 'active' AND price BETWEEN 10 AND 50
// Index: (status, price)

// Query: User's recent orders
// SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10
// Index: (user_id, created_at DESC)

Index Trade-offs

+-------------------+----------------------------+----------------------------+
| Aspect            | More Indexes               | Fewer Indexes              |
+-------------------+----------------------------+----------------------------+
| SELECT queries    | Faster (index lookups)     | Slower (table scans)       |
| INSERT/UPDATE     | Slower (must update indexes)| Faster (less overhead)    |
| Storage           | More disk space            | Less disk space            |
| Writes per second | Lower throughput           | Higher throughput          |
+-------------------+----------------------------+----------------------------+

Rule of thumb:
- Read-heavy apps (blogs, e-commerce): More indexes
- Write-heavy apps (analytics, logging): Fewer, targeted indexes

Composite Index Column Order

Rule: Put the most selective (most unique values) column first,
      BUT put equality conditions before range conditions.

Example: Products table

Good:  INDEX (status, category_id, price)
  WHERE status = 'active'                    -- uses index
  AND category_id = 5                        -- uses index
  AND price BETWEEN 10 AND 50               -- uses index (range last)

Bad:   INDEX (price, status, category_id)
  WHERE status = 'active'                    -- can't use index (price is first)
  AND category_id = 5                        -- can't use index
  AND price BETWEEN 10 AND 50               -- uses index for price only

The composite index is used left-to-right.
Once a range condition is hit, subsequent columns can't use the index.

Pagination Patterns

How you paginate data affects both database performance and user experience.

Offset Pagination

// Classic offset pagination
// GET /api/products?page=5&limit=20

async function getProducts(page, limit) {
  const offset = (page - 1) * limit;

  const products = await db.query(
    'SELECT * FROM products ORDER BY id LIMIT ? OFFSET ?',
    [limit, offset]
  );

  const [{ total }] = await db.query(
    'SELECT COUNT(*) as total FROM products'
  );

  return {
    data: products,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
    },
  };
}

Offset Pagination Problem:
Page 1:   OFFSET 0    -> Fast (skip 0 rows)
Page 10:  OFFSET 180  -> OK (skip 180 rows)
Page 100: OFFSET 1980 -> Slow (skip 1980 rows)
Page 1000: OFFSET 19980 -> Very slow (skip 19980 rows)

The database must scan and discard all offset rows.
Performance degrades linearly with page number.

Cursor-Based Pagination (Keyset Pagination)

// Cursor pagination — constant performance regardless of page
// GET /api/products?cursor=abc123&limit=20

async function getProducts(cursor, limit) {
  let query;
  let params;

  if (cursor) {
    // Decode cursor (base64-encoded last seen ID)
    const lastId = Buffer.from(cursor, 'base64').toString();

    query = `
      SELECT * FROM products
      WHERE id > ?
      ORDER BY id
      LIMIT ?
    `;
    params = [lastId, limit + 1]; // Fetch one extra to check hasMore
  } else {
    query = 'SELECT * FROM products ORDER BY id LIMIT ?';
    params = [limit + 1];
  }

  const results = await db.query(query, params);
  const hasMore = results.length > limit;
  const products = results.slice(0, limit);

  return {
    data: products,
    pagination: {
      nextCursor: hasMore
        ? Buffer.from(String(products[products.length - 1].id)).toString('base64')
        : null,
      hasMore,
    },
  };
}

Cursor Pagination Performance:
Page 1:    WHERE id > 0 LIMIT 20      -> Fast (index seek)
Page 10:   WHERE id > 180 LIMIT 20    -> Fast (index seek)
Page 100:  WHERE id > 1980 LIMIT 20   -> Fast (index seek)
Page 1000: WHERE id > 19980 LIMIT 20  -> Fast (index seek)

Constant O(1) performance because it uses the index directly.
No scanning/discarding of rows.

Comparison

+--------------------+--------------------+--------------------+
| Aspect             | Offset             | Cursor             |
+--------------------+--------------------+--------------------+
| Performance        | Degrades with page | Constant           |
| Jump to page N     | Yes                | No (sequential)    |
| Total count        | Easy               | Expensive          |
| New records added  | May show duplicates| Consistent         |
| Implementation     | Simple             | More complex       |
| Best for           | Small datasets,    | Large datasets,    |
|                    | admin panels       | infinite scroll    |
+--------------------+--------------------+--------------------+

Frontend Pagination Component

// Offset pagination — traditional page numbers
function PagedList({ fetchFn }) {
  const [page, setPage] = useState(1);
  const [data, setData] = useState({ items: [], totalPages: 0 });

  useEffect(() => {
    fetchFn(page).then(setData);
  }, [page, fetchFn]);

  return (
    <div>
      <ul>
        {data.items.map(item => <li key={item.id}>{item.name}</li>)}
      </ul>

      <nav>
        <button disabled={page <= 1} onClick={() => setPage(p => p - 1)}>
          Previous
        </button>
        <span>Page {page} of {data.totalPages}</span>
        <button disabled={page >= data.totalPages} onClick={() => setPage(p => p + 1)}>
          Next
        </button>
      </nav>
    </div>
  );
}

// Cursor pagination — infinite scroll
function InfiniteList({ fetchFn }) {
  const [items, setItems] = useState([]);
  const [cursor, setCursor] = useState(null);
  const [hasMore, setHasMore] = useState(true);
  const [loading, setLoading] = useState(false);
  const observerRef = useRef(null);

  const loadMore = useCallback(async () => {
    if (loading || !hasMore) return;
    setLoading(true);

    const result = await fetchFn(cursor);
    setItems(prev => [...prev, ...result.data]);
    setCursor(result.pagination.nextCursor);
    setHasMore(result.pagination.hasMore);
    setLoading(false);
  }, [cursor, hasMore, loading, fetchFn]);

  // Intersection Observer for infinite scroll
  useEffect(() => {
    const observer = new IntersectionObserver(
      ([entry]) => {
        if (entry.isIntersecting) loadMore();
      },
      { rootMargin: '200px' }
    );

    if (observerRef.current) observer.observe(observerRef.current);
    return () => observer.disconnect();
  }, [loadMore]);

  return (
    <div>
      <ul>
        {items.map(item => <li key={item.id}>{item.name}</li>)}
      </ul>
      <div ref={observerRef}>
        {loading && <p>Loading...</p>}
        {!hasMore && <p>No more items</p>}
      </div>
    </div>
  );
}

Query Result Caching

Cache expensive query results to avoid hitting the database on every request.

Server-Side Query Cache

// Simple in-memory query cache with TTL
class QueryCache {
  constructor() {
    this.cache = new Map();
  }

  async get(key, queryFn, ttl = 60000) {
    const cached = this.cache.get(key);

    if (cached && Date.now() - cached.timestamp < ttl) {
      return cached.data;
    }

    const data = await queryFn();
    this.cache.set(key, { data, timestamp: Date.now() });
    return data;
  }

  invalidate(key) {
    this.cache.delete(key);
  }

  invalidatePattern(pattern) {
    for (const key of this.cache.keys()) {
      if (key.includes(pattern)) {
        this.cache.delete(key);
      }
    }
  }
}

const queryCache = new QueryCache();

// Usage in API route
app.get('/api/products', async (req, res) => {
  const { category } = req.query;
  const cacheKey = `products:category:${category}`;

  const products = await queryCache.get(
    cacheKey,
    () => db.query('SELECT * FROM products WHERE category_id = ?', [category]),
    30000 // 30 second TTL
  );

  res.json(products);
});

// Invalidate when data changes
app.post('/api/products', async (req, res) => {
  const product = await db.insert('products', req.body);

  // Clear related caches
  queryCache.invalidatePattern('products:');

  res.json(product);
});

Redis-Based Query Cache

// For distributed systems, use Redis instead of in-memory cache
const Redis = require('ioredis');
const redis = new Redis();

async function cachedQuery(key, queryFn, ttl = 60) {
  // Try cache first
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  // Cache miss — run query
  const result = await queryFn();

  // Store in Redis with TTL
  await redis.setex(key, ttl, JSON.stringify(result));

  return result;
}

// Usage
app.get('/api/products/:id', async (req, res) => {
  const product = await cachedQuery(
    `product:${req.params.id}`,
    () => db.query('SELECT * FROM products WHERE id = ?', [req.params.id]),
    300 // 5 minute TTL
  );

  res.json(product);
});

Handling Large Datasets on the Frontend

Virtual Scrolling

// Render only visible items, not thousands of DOM nodes
import { useRef, useState, useEffect, useCallback } from 'react';

function VirtualList({ items, itemHeight, containerHeight }) {
  const [scrollTop, setScrollTop] = useState(0);
  const containerRef = useRef(null);

  const totalHeight = items.length * itemHeight;
  const startIndex = Math.floor(scrollTop / itemHeight);
  const endIndex = Math.min(
    startIndex + Math.ceil(containerHeight / itemHeight) + 1,
    items.length
  );

  const visibleItems = items.slice(startIndex, endIndex);

  const handleScroll = useCallback((e) => {
    setScrollTop(e.target.scrollTop);
  }, []);

  return (
    <div
      ref={containerRef}
      onScroll={handleScroll}
      style={{ height: containerHeight, overflow: 'auto' }}
    >
      <div style={{ height: totalHeight, position: 'relative' }}>
        {visibleItems.map((item, i) => (
          <div
            key={item.id}
            style={{
              position: 'absolute',
              top: (startIndex + i) * itemHeight,
              height: itemHeight,
              width: '100%',
            }}
          >
            {item.name}
          </div>
        ))}
      </div>
    </div>
  );
}

// Usage: Render 100,000 items without DOM bloat
<VirtualList
  items={bigDataset}       // 100,000 items
  itemHeight={40}          // Each row is 40px
  containerHeight={600}    // Visible area is 600px
/>
// Only ~16 DOM nodes rendered at any time instead of 100,000

Incremental Loading Pattern

// Load data in chunks as the user needs it
async function loadIncrementally(fetchChunk, onChunk) {
  let page = 0;
  let hasMore = true;

  while (hasMore) {
    const { data, pagination } = await fetchChunk(page);
    onChunk(data);
    hasMore = pagination.hasMore;
    page++;

    // Yield to the main thread between chunks
    await new Promise(resolve => setTimeout(resolve, 0));
  }
}

// Usage
const allItems = [];
await loadIncrementally(
  (page) => fetch(`/api/items?page=${page}&limit=100`).then(r => r.json()),
  (chunk) => {
    allItems.push(...chunk);
    renderProgress(allItems.length);
  }
);

Selecting Only Needed Fields

// Bad: Fetch everything, use 3 fields
// GET /api/products
// Returns: id, name, description, price, images, reviews, metadata, ...
// Response size: 500KB for 20 products

// Good: Fetch only what you need
// GET /api/products?fields=id,name,price
// Returns: id, name, price
// Response size: 5KB for 20 products

// Backend implementation
app.get('/api/products', async (req, res) => {
  const fields = req.query.fields?.split(',') || ['*'];

  // Validate allowed fields
  const allowedFields = ['id', 'name', 'price', 'category', 'image_url'];
  const selectedFields = fields.filter(f => allowedFields.includes(f));

  const products = await db.query(
    `SELECT ${selectedFields.join(', ')} FROM products LIMIT 20`
  );

  res.json(products);
});

Query Design Best Practices

SELECT Only What You Need

-- Bad: Select everything
SELECT * FROM products;

-- Good: Select specific columns
SELECT id, name, price, image_url FROM products;

-- Why: Reduces data transfer, memory usage, and allows covering indexes

Use EXPLAIN to Analyze Queries

-- See how the database plans to execute your query
EXPLAIN ANALYZE SELECT * FROM products
WHERE category_id = 5
AND price BETWEEN 10 AND 50
ORDER BY created_at DESC
LIMIT 20;

-- Output tells you:
-- 1. Whether an index is used (Index Scan vs Sequential Scan)
-- 2. Estimated vs actual rows scanned
-- 3. Execution time breakdown
-- 4. Sort method (in-memory vs disk)

// Log slow queries in development
const SLOW_QUERY_THRESHOLD = 100; // ms

async function query(sql, params) {
  const start = Date.now();
  const result = await db.query(sql, params);
  const duration = Date.now() - start;

  if (duration > SLOW_QUERY_THRESHOLD) {
    console.warn(`SLOW QUERY (${duration}ms):`, sql);
    console.warn('Params:', params);
  }

  return result;
}

Key Takeaways

• The N+1 problem is the most common database performance issue — use JOINs or batch loading
• Indexes turn O(n) full-table scans into O(log n) lookups — index your WHERE, JOIN, and ORDER BY columns
• Cursor-based pagination has constant performance regardless of page depth — use it for large datasets
• Offset pagination is simpler but degrades on deep pages — acceptable for small datasets
• Cache expensive query results with TTL — invalidate when underlying data changes
• SELECT only the columns you need — reduces data transfer and enables covering indexes
• Virtual scrolling renders only visible items — handles 100K+ rows without DOM bloat
• Use EXPLAIN to understand how the database executes your queries
• Design APIs that let the frontend request only the data it needs (field selection, pagination)
• Log slow queries in development to catch performance issues early