Database Performance Optimization: A Deep Dive into Indexing, Caching, and Query Optimization

Database performance is the hidden culprit behind most slow applications I get called in to fix. The symptom is always "the app is slow." The diagnosis is almost always: full table scans, N+1 queries, no caching, or queries that could be hitting a read replica but are hammering the primary.

This guide covers every technique I apply in production — with real query examples, EXPLAIN output, and code you can use today.

→ Understanding How MySQL Executes Queries

Before optimising anything, you need to understand how the database processes a query. MySQL's query optimiser chooses an execution plan — and if it makes bad choices (usually because indexes are missing), performance degrades catastrophically.

EXPLAIN ANALYZE shows you exactly what MySQL is doing:

sql

EXPLAIN ANALYZE
SELECT o.id, o.total, o.status, o.created_at, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL 7 DAY
ORDER BY o.created_at DESC
LIMIT 50;

Without indexes, the output shows:

-> Limit: 50 row(s)
   -> Sort: o.created_at DESC
      -> Filter: (o.status = 'pending' AND o.created_at > ...)
         -> Table scan on o  (cost=450231.25 rows=4476432)  ← FULL TABLE SCAN

MySQL scanned 4.4 million rows to return 50. This query alone can lock the database.

After adding the right composite index:

sql

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

The EXPLAIN output becomes:

-> Limit: 50 row(s)
   -> Index range scan on o using idx_orders_status_created  (cost=18.5 rows=50)

From 4.4 million scans to 50. This is the difference between a 4-second query and a 2-millisecond query.

→ Step 1: Enable the Slow Query Log

Before optimising, measure. Enable MySQL's slow query log:

ini

# /etc/mysql/conf.d/performance.cnf
[mysqld]
slow_query_log                = 1
slow_query_log_file           = /var/log/mysql/slow-queries.log
long_query_time               = 0.5   # Log queries slower than 500ms
log_queries_not_using_indexes = 1     # Log ALL full table scans
min_examined_row_limit        = 1000  # Only if more than 1000 rows examined

Analyse with mysqldumpslow:

bash

mysqldumpslow -s t -t 10 /var/log/mysql/slow-queries.log  # top 10 slowest
mysqldumpslow -s r -t 10 /var/log/mysql/slow-queries.log  # most rows scanned

In Laravel development, install Laravel Debugbar — it shows every query on every page load with execution time and source line.

→ Step 2: Strategic Composite Indexes

A B-tree index is a sorted data structure. MySQL can binary-search it in O(log n) instead of scanning every row O(n). For 10 million rows, that is checking 23 nodes vs. 10 million rows.

The leftmost prefix rule — a composite index on (status, created_at) can be used for:

WHERE status = ? — yes
WHERE status = ? AND created_at > ? — yes
WHERE created_at > ? alone — no (skips the first column)

Real-world index patterns:

sql

-- CRM: lead pipeline queries
CREATE INDEX idx_leads_assigned_stage_created
    ON leads (assigned_to, stage, created_at DESC);

-- Fintech: transaction history per user
CREATE INDEX idx_transactions_user_date
    ON transactions (user_id, created_at DESC, status);

-- Booking system: availability lookups
CREATE INDEX idx_slots_service_start_available
    ON availability_slots (service_id, slot_start, is_available);

-- Multi-tenant SaaS: always include tenant_id first
CREATE INDEX idx_invoices_tenant_status_due
    ON invoices (tenant_id, status, due_date);

Covering indexes — when the index contains all columns a query needs, MySQL never touches the main table:

sql

-- Index that covers the entire SELECT
CREATE INDEX idx_orders_covering
    ON orders (status, created_at DESC, id, user_id, total);

-- This query reads ONLY from the index
SELECT id, user_id, total, created_at
FROM orders WHERE status = 'pending'
ORDER BY created_at DESC LIMIT 50;

→ Step 3: Eliminating N+1 Queries

N+1 is when you load a list of records, then execute a separate query for each record's related data. This is the single most impactful performance fix in most Laravel applications.

php

// N+1 — 1001 queries for 500 orders
$orders = Order::limit(500)->get();
foreach ($orders as $order) {
    echo $order->user->name;   // NEW query on every iteration
    echo $order->product->sku; // Another new query
}

// Eager loading — 3 queries total, regardless of count
$orders = Order::with([
    'user:id,name,email',
    'product:id,sku,name'
])->limit(500)->get();
foreach ($orders as $order) {
    echo $order->user->name;   // No query — already loaded
    echo $order->product->sku; // No query — already loaded
}

Auto-detect N+1 in development:

php

// AppServiceProvider::boot()
if (app()->isLocal()) {
    Model::preventLazyLoading(); // throws exception if you forget eager loading
}

Chunking large datasets:

php

// Loads 100,000 rows into memory — will exhaust RAM
$orders = Order::with('user')->where('status', 'pending')->get();

// Processes 500 rows at a time — constant memory usage
Order::with('user:id,name,email')
     ->where('status', 'pending')
     ->chunkById(500, function (Collection $chunk) {
         foreach ($chunk as $order) {
             ProcessOrder::dispatch($order);
         }
     });

Use chunkById not chunk — it uses the ID for pagination instead of OFFSET, which degrades at high offsets.

→ Step 4: Redis Caching Patterns

Caching is not about making fast things faster. It is about making expensive operations run once instead of on every request.

The cache-aside pattern:

php

$value = Cache::remember($key, $ttl, fn() => $expensiveOperation());
// Hit: returns from Redis in microseconds
// Miss: runs the closure, stores result in Redis, returns it

CRM dashboard stats:

php

public function getDashboardStats(User $user): array
{
    return Cache::remember("dashboard.stats.{$user->id}", 300, function () use ($user) {
        return [
            'total_leads'       => Lead::where('assigned_to', $user->id)->count(),
            'new_this_week'     => Lead::where('assigned_to', $user->id)
                                       ->where('created_at', '>=', now()->startOfWeek())
                                       ->count(),
            'pipeline_value'    => Lead::where('assigned_to', $user->id)
                                       ->whereIn('stage', ['proposal', 'negotiation'])
                                       ->sum('estimated_value'),
        ];
    });
}

Cache invalidation with Redis tags:

php

// Cache with tags
Cache::tags(['products', 'category-5'])->remember(
    "products.category.5.page.1",
    3600,
    fn() => Product::where('category_id', 5)->paginate(24)
);

// Flush all product caches when any product changes
public function updateProduct(Product $product, array $data): void
{
    $product->update($data);
    Cache::tags(['products'])->flush();
}

Caching external API responses (e.g. eSewa exchange rates):

php

public function getExchangeRate(string $from, string $to): float
{
    return (float) Cache::remember("exchange_rate.{$from}.{$to}", 3600, function () use ($from, $to) {
        $response = Http::timeout(5)
            ->retry(3, 100)
            ->get("https://api.exchangerate.host/latest", [
                'base' => $from, 'symbols' => $to
            ]);

        if ($response->failed()) throw new RuntimeException("Exchange rate API unavailable");

        return $response->json("rates.{$to}");
    });
}

→ Step 5: Read Replicas

In a typical application, reads outnumber writes by 4:1 to 10:1. With Laravel's read/write connection configuration, reads automatically route to replicas:

php

'mysql' => [
    'write' => ['host' => env('DB_WRITE_HOST')],
    'read'  => [
        ['host' => env('DB_READ_HOST_1')],
        ['host' => env('DB_READ_HOST_2')], // round-robin
    ],
    'sticky' => true, // After a write, reads in the same request use write connection
],

Force a specific connection when needed:

php

// Time-sensitive: always read fresh balance from primary
$freshBalance = DB::connection('mysql::write')
    ->table('accounts')->where('id', $accountId)->value('balance');

// Heavy analytics: never impact writes
$report = DB::connection('mysql::read')
    ->table('transactions')
    ->selectRaw('DATE(created_at) as date, SUM(amount) as total')
    ->whereBetween('created_at', [$start, $end])
    ->groupBy('date')->get();

→ Measured Results from Real Projects

After applying these techniques to Applyjob Nepal:

Query	Before	After
Job search	2,100ms	85ms
Dashboard stats	3,400ms	290ms
Monthly report	45s	4s

Peak concurrent users without slowdown increased from 50 to 800+.

✨

Topics

#fintech integration developer#CRM systems#marketplace system developer#Database Performance#Query Optimization

💡

Need Help With This?

Let's talk about how I can architect a scalable solution for your business.

Get In Touch →