When a web project is first launched, everything seems perfect. Tables are empty, and database queries respond in just a few milliseconds. However, as your project succeeds and traffic begins to surge, with data growing into millions of rows, you suddenly notice your site slowing down and server CPU usage maxing out.

In this scenario, most developers resort to "Vertical Scaling" by upgrading the server's RAM or processor. But pouring money into hardware only masks the problem. The real solution is identifying the bottlenecks in the database and fixing them at the query and architecture level.

In this guide we cover the techniques that make the biggest difference in high-traffic systems: finding slow queries with EXPLAIN, eliminating N+1 queries, indexing correctly, paginating large tables efficiently, and caching hot data in Redis without serving stale content.

1. Measure First: Find Slow Queries With EXPLAIN

Optimization without measurement is guesswork. Before changing anything, find out which queries are actually slow.

  • Enable the slow query log in MySQL/MariaDB (for example long_query_time = 1) to catch queries that take longer than a second in production.
  • Use your framework's debug toolbar during development to see how many queries a page runs and how long each takes.
  • Run EXPLAIN on a suspicious query to see how the database plans to execute it.
EXPLAIN
SELECT id, title
FROM posts
WHERE category_id = 12
ORDER BY created_at DESC
LIMIT 20;

In the output, watch these columns: type = ALL means a full table scan, key = NULL means no index is used, a large rows value means many rows are examined, and Using filesort means the result is sorted in a separate step. These are the queries worth optimizing first.

2. The Silent Killer: The N+1 Query Problem

The N+1 problem is the most common and fastest server-exhausting trap, especially when using ORMs (Object-Relational Mapping) and frameworks. Each query is fast on its own; the problem is how many of them run.

Suppose you want to list 50 blog posts on the screen and display the category of each post. If you load the relationship lazily inside a loop, the system works like this:

  1. 1 main query is executed to fetch the posts: SELECT * FROM posts LIMIT 50; (1 query)
  2. Inside the loop, a separate query is executed against the categories table for each of the 50 posts: SELECT * FROM categories WHERE id = ?; (50 queries)

A total of 51 queries are executed. If you list 1,000 records on a page, you fire 1,001 queries in a single page load. The solution is to use eager loading or a JOIN with the Query Builder.

// ❌ BAD PRACTICE (creates the N+1 problem)
$posts = $postModel->findAll();
foreach ($posts as $post) {
    // A new query runs on every loop iteration!
    echo $categoryModel->find($post->category_id)->name;
}

// ✅ GOOD PRACTICE (JOIN with CodeIgniter 4 Query Builder)
$posts = $postModel->select('posts.*, categories.name AS category_name')
                   ->join('categories', 'categories.id = posts.category_id')
                   ->findAll();

foreach ($posts as $post) {
    // No extra trips to the database; the data is already loaded.
    echo $post->category_name;
}

If a JOIN is not practical, the alternative is to collect the IDs first and load the related rows with a single WHERE id IN (...) query. Either way, the number of queries stays constant no matter how many rows you display.

3. The Power and Cost of SQL Indexing

Imagine you have 2 million users in your database. When a user tries to log in and you run WHERE email = 'test@codeblanks.com', without an index the database engine (MySQL/PostgreSQL) has to scan all 2 million rows until it finds a match. This is called a full table scan, and it puts heavy pressure on disk I/O and CPU.

If you add an index to the email column, the database keeps a sorted B-Tree (a balanced tree structure, not a binary tree) for that column, similar to an alphabetical directory. Instead of reading millions of rows, it walks down a few levels of the tree and finds the row almost immediately. Search times drop from seconds to milliseconds.

-- Create an index on a frequently searched column
CREATE INDEX idx_users_email ON users (email);

Composite Indexes and Column Order

Many real queries filter on one column and sort by another. A composite index can cover both, but column order matters: the index can only be used from its leftmost column onward (the "leftmost prefix" rule).

-- Matches: WHERE category_id = ? ORDER BY created_at DESC
CREATE INDEX idx_posts_category_created ON posts (category_id, created_at);

This index helps queries that filter by category_id, and queries that filter by category_id and sort by created_at. It does not help a query that only filters by created_at.

Queries That Cannot Use an Index

  • Wrapping the column in a function: WHERE YEAR(created_at) = 2026. Use a range instead: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'.
  • Leading wildcards: LIKE '%term' cannot use a normal B-Tree index. Consider a full-text index for search.
  • Comparing a string column with a number, which forces a type conversion on every row.

⚠️ The cost of indexes: do not index every column. Indexes speed up reads (SELECT) but slow down writes (INSERT/UPDATE/DELETE), because every index has to be updated when the data changes, and they use extra disk space and memory. Index the columns you frequently use in WHERE, ORDER BY and JOIN clauses, and confirm the result with EXPLAIN.

4. Paginate Large Tables Without OFFSET

Classic pagination with LIMIT 20 OFFSET 100000 looks harmless, but the database still has to read and throw away the first 100,000 rows. Deep pages get slower and slower as the table grows.

Keyset pagination (also called cursor pagination) solves this by remembering where the previous page ended:

-- ❌ Slow on deep pages
SELECT id, title FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination: continue after the last id you showed
SELECT id, title FROM posts
WHERE id < :last_seen_id
ORDER BY id DESC
LIMIT 20;

With an index on the sort column, every page is as fast as the first one. The trade-off is that users cannot jump directly to page 5,000, which is rarely needed for feeds, infinite scroll and APIs.

5. Shifting From Disk to Memory: Redis Caching Strategies

No matter how well you optimize your queries, running the same query again and again for data that is read often but changes rarely is wasted work. Typical examples are the latest posts on the homepage, site settings, translation strings and category lists. Reading from memory is much faster than reading from disk, even an SSD. This is where Redis, an in-memory data store, comes into play.

The most common pattern is cache-aside: check the cache first, and only go to the database on a miss. CodeIgniter 4 supports Redis as a cache handler out of the box:

$cache    = \Config\Services::cache();
$cacheKey = 'homepage_latest_posts';

// STEP 1: Check if the data is already in the cache
if (! $posts = $cache->get($cacheKey)) {

    // STEP 2: Cache miss, so read from the database
    $posts = $postModel->where('status', 'published')
                       ->orderBy('created_at', 'DESC')
                       ->findAll(10);

    // STEP 3: Store the result in Redis for 1 hour (3600 seconds)
    $cache->save($cacheKey, $posts, 3600);
}

return view('home', ['posts' => $posts]);

Cache Invalidation

The real trick is not writing data to the cache but keeping it correct. What happens if you publish a new post from the admin panel? Visitors will not see it for up to an hour. The fix is to delete the related cache key whenever the underlying data changes:

// AdminController.php: when a new post is added
$postModel->insert($newPostData);

// Delete the old cache entry because the data changed.
// The next visitor triggers a fresh query and the cache is rebuilt.
\Config\Services::cache()->delete('homepage_latest_posts');

Choosing a TTL and Avoiding a Cache Stampede

  • Match the TTL to how fresh the data must be. Settings can live for hours; a stock count may need seconds or no cache at all.
  • Use clear, versioned keys such as posts:latest:en:v2, so you can invalidate a whole group by changing the version.
  • Watch out for a cache stampede. When a popular key expires, hundreds of requests can hit the database at the same moment. Add a small random value to the TTL, or let only one request rebuild the value while others briefly serve the old one.
  • Do not cache everything. User-specific or rarely read data often costs more to cache than to query.

6. When One Database Server Is Not Enough

After queries, indexes and caching are in good shape, the next steps are architectural:

  • Read replicas: send read-heavy traffic to one or more replicas and keep writes on the primary server.
  • Background jobs: move slow work such as sending emails, generating reports or resizing images out of the request into a queue.
  • Precomputed data: store counters and aggregates (like comment counts) instead of calculating them with COUNT(*) on every page view.

Quick Checklist

  • Is the slow query log enabled, and do you review it regularly?
  • Does any page run a query inside a loop (N+1)?
  • Do the columns used in WHERE, JOIN and ORDER BY have suitable indexes, confirmed with EXPLAIN?
  • Do queries select only the columns they need instead of SELECT *?
  • Do large lists use keyset pagination instead of deep OFFSETs?
  • Is frequently read data cached, and is the cache cleared when that data changes?

Conclusion

High-traffic systems are managed not by brute force (more RAM, more CPU) but by smart, sustainable architecture. Measure first, eliminate N+1 queries, index the right columns, paginate efficiently and cache hot data in Redis with a clear invalidation strategy. These steps let a single server handle far more traffic than you might expect. For more on writing readable queries, see our guide on the golden rules of clean code.