how to systematically optimize Laravel databases in production

Your Laravel application felt fast during development.

Pages loaded instantly. Queries returned results in milliseconds. Everything seemed under control.

Then you deployed to production.

Traffic increased. Data grew. Users started complaining: “The site feels slow.”

This is a classic Laravel problem — and no, it’s usually not caused by PHP or Blade templates.

👉 The real bottleneck is almost always the database.

In production, inefficient queries don’t just slow down a page — they compound under load, drain server resources, and quietly kill performance.

In this guide, you’ll learn how to systematically optimize Laravel databases in production using three essential tools:

Database Indexes
EXPLAIN (Query Execution Plans)
MySQL Slow Query Log

Used together, these tools turn guessing into measurable optimization.

Why Database Optimization Matters (More Than You Think)

A few uncomfortable truths:

A 1-second delay can reduce conversions by 7%
Full table scans grow exponentially with data
What works with 10,000 rows fails miserably at 1 million
Laravel doesn’t automatically fix bad queries

Your database doesn’t care how elegant your Eloquent code looks — it only cares how much work it has to do.

Optimization is about reducing work.

Database Indexes: The Foundation of Performance

What Is an Index (Really)?

Without an index, MySQL must scan every row to find matching data.

Think of it like this:

No index → Read every row (slow)
With index → Jump directly to the data (fast)

Indexes turn O(n) scans into O(log n) lookups.

When Should You Add Indexes?

Create indexes on columns that are:

Used frequently in WHERE
Used in JOIN conditions
Used in ORDER BY or GROUP BY
High in uniqueness (not just 0/1 values)

Avoid indexing:

Rarely used columns
Columns updated constantly
Low-cardinality boolean fields (unless part of composite index)

Basic Laravel Example

$orders = Order::where('user_id', $userId)->get();

If orders.user_id is not indexed, MySQL scans the entire table.

Fix via Migration

Schema::table('orders', function (Blueprint $table) {
    $table->index('user_id');
});

Now MySQL can jump straight to relevant rows.

Composite Indexes (Where Most Apps Win)

Real queries rarely filter on just one column.

$orders = Order::where('user_id', $userId)
               ->where('status', 'paid')
               ->orderBy('created_at', 'desc')
               ->get();

Optimal Index

Schema::table('orders', function (Blueprint $table) {
    $table->index(['user_id', 'status', 'created_at']);
});

⚠️ Index order matters

MySQL can use (user_id, status)
It cannot efficiently use (status, created_at) alone

Always index columns in the same order your queries filter them.

Indexing Mistakes to Avoid

❌ Indexing every column
❌ Guessing instead of measuring
❌ Ignoring write performance
❌ Indexing low-cardinality fields alone

Indexes speed up reads but slow down writes. Balance is key.

EXPLAIN: Understanding What MySQL Actually Does

Writing a query doesn’t mean MySQL executes it the way you expect.

EXPLAIN shows the truth.

Using EXPLAIN in Laravel

Raw SQL (Recommended)

$plan = DB::select(
    'EXPLAIN SELECT * FROM orders WHERE user_id = ?',
    [$userId]
);

dd($plan);

MySQL Console

EXPLAIN SELECT * FROM orders WHERE user_id = 10;

The Most Important EXPLAIN Columns

`type` (Scan Method)

ALL → Full table scan ❌
index → Full index scan
range → Range scan
ref → Indexed lookup ✅
const → Single-row lookup ✅✅

`key`

The index actually used
NULL = no index used ❌

`rows`

Estimated rows scanned
Smaller is always better

`Extra`

Using filesort → Slow sorting
Using temporary → Temp table created
Using index → Index-only query (excellent)

Example: Query Without Index

EXPLAIN SELECT * FROM products WHERE category_id = 5;

Result:

type = ALL
key = NULL
rows = 600000

🚨 MySQL scanned the entire table.

After Adding Index

CREATE INDEX idx_category_id ON products (category_id);

EXPLAIN SELECT * FROM products WHERE category_id = 5;

Result:

type = ref
key = idx_category_id
rows = 120

✔ Massive improvement with zero code changes.

EXPLAIN Golden Rules

Avoid type = ALL
Ensure key is not NULL
Minimize rows
Eliminate Using filesort when possible

Slow Query Log: Catching Problems in Real Traffic

Some performance issues only appear in production.

That’s where Slow Query Log shines.

What Is Slow Query Log?

It records queries that exceed a time threshold.

Think of it as a black box recorder for your database.

Enable Slow Query Log (Temporary)

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;

Queries taking longer than 1 second will be logged.

Enable Permanently (Recommended)

Edit MySQL config:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Restart MySQL:

sudo systemctl restart mysql

Sample Slow Query Log Entry

Query_time: 2.94
Rows_examined: 184732
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;

This query scanned 184,732 rows to return a few records.

That’s your optimization target.

Analyze Slow Queries

Built-in Tool

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Professional Tool (Recommended)

pt-query-digest /var/log/mysql/mysql-slow.log

This gives:

Query frequency
Total execution time
Average latency
Rows examined

Laravel-Level Monitoring

Laravel Telescope (Great for QA)

composer require laravel/telescope
php artisan telescope:install
php artisan migrate

View query execution time directly in the dashboard.

Debugbar (Local Only)

composer require barryvdh/laravel-debugbar --dev

Never use Debugbar in production.

A Real Optimization Workflow

Enable slow query log
Identify worst queries
Run EXPLAIN
Add or adjust indexes
Refactor queries if needed
Measure before & after
Deploy and monitor

Optimization without measurement is guesswork.

Real-World Case: Product Search Optimization

The Problem Query

Product::where('name', 'LIKE', '%laptop%')
       ->where('is_active', 1)
       ->orderBy('created_at', 'desc')
       ->paginate(20);

EXPLAIN showed:

Full table scan
Filesort
800k rows scanned

Fix 1: Full-Text Search

Schema::table('products', function (Blueprint $table) {
    $table->fullText('name');
});

Product::whereFullText('name', 'laptop')
       ->where('is_active', 1)
       ->paginate(20);

Fix 2: Composite Index

Schema::table('products', function (Blueprint $table) {
    $table->index(['is_active', 'created_at']);
});

Results

| Metric       | Before  | After   |
| ------------ | ------- | ------- |
| Rows scanned | 850,000 | 220     |
| Query time   | 3.1s    | 0.04s   |
| CPU usage    | High    | Minimal |

Same app. Same data.
Just smarter database usage.