📁 Volume II: Laravel Performance Tuning Kit

🔍 Topic 9: Database Indexing & EXPLAIN

The #1 database performance killer (and how to fix it).

"You can write the most beautiful Eloquent code in the world.
But if your WHERE clause searches an unindexed column, the database reads every single row.
Full table scan. Every time. 5 seconds for 1 million rows.
And you blame Laravel."
⚠️ THE MOST COMMON DATABASE DISASTER

A missing index is responsible for 90% of database performance problems in production Laravel apps. Developers add indexes in development (where they have 100 rows) and forget them in production (where they have 1,000,000 rows). The result: queries that take 5 seconds instead of 0.001 seconds.

🔴 The Problem: Full Table Scan

WHAT IS A FULL TABLE SCAN?

When you query WHERE email = 'user@example.com' and there's NO index on email, the database:

  1. Starts at row 1
  2. Reads the email column
  3. Compares with your value
  4. Moves to row 2
  5. Repeats for EVERY SINGLE ROW in the table
│ │ FULL TABLE SCAN (No Index) — 1,000,000 rows │ ═══════════════════════════════════════════════════════════════════ │ │ SELECT * FROM users WHERE email = 'john@example.com' │ │ ┌─────────────────────────────────────────────────────────────────┐ │ │ Row 1: email = 'alice@x.com' → NO │ │ │ Row 2: email = 'bob@x.com' → NO │ │ │ Row 3: email = 'carol@x.com' → NO │ │ │ Row 4: email = 'david@x.com' → NO │ │ │ ... │ │ │ Row 500,000: email = 'john@example.com' → YES! Found! │ │ │ ... But the database doesn't know it's the only match... │ │ │ Row 500,001: email = 'kate@x.com' → NO │ │ │ ... │ │ │ Row 1,000,000: email = 'zoe@x.com' → NO │ │ └─────────────────────────────────────────────────────────────────┘ │ │ Rows examined: 1,000,000 │ Time: ~2-5 seconds │ │ ═══════════════════════════════════════════════════════════════════ │ │ INDEX SCAN (With Index) — 1,000,000 rows │ ═══════════════════════════════════════════════════════════════════ │ │ ┌─────────────────────────────────────────────────────────────────┐ │ │ Index on email (B-Tree): │ │ │ │ │ │ ┌─────────────┐ │ │ │ │ 'm' │ │ │ │ ┌──┴─────────────┴──┐ │ │ │ ┌────┴────┐ ┌────┴────┐ │ │ │ │ 'f' │ │ 't' │ │ │ │ ┌──┴──┐ ┌──┴──┐ ┌──┴──┐ ┌──┴──┐ │ │ │ 'a'... 'e'... 'j'... 'm'... 's'... 'z'... │ │ │ │ │ │ │ └── 'john@example.com' — found in ~log₂(N) steps │ │ └─────────────────────────────────────────────────────────────────┘ │ │ Rows examined: ~20 (log₂ of 1,000,000) │ Time: ~0.001 seconds │
THE DIFFERENCE

1,000,000 rows examined vs 20 rows examined = 50,000x faster. An index transforms a 5-second query into a 0.0001-second query.

📊 How Query Time Scales With and Without Indexes

Table Size (rows)Without Index (Full Scan)With Index (B-Tree)Difference
1,000 rows 0.005 sec 0.0005 sec 10x slower
10,000 rows 0.05 sec 0.0005 sec 100x slower
100,000 rows 0.5 sec 0.0005 sec 1,000x slower
1,000,000 rows 5 sec 0.0005 sec 10,000x slower
10,000,000 rows 50 sec 0.001 sec 50,000x slower
THE TRAGEDY

In development with 1,000 rows, you don't notice missing indexes. Everything is fast. In production with 1,000,000 rows, the same query takes 5 seconds. Your users complain. Your server times out. All because you forgot one CREATE INDEX command.

✅ The Fix: CREATE INDEX

THE GOLDEN RULE OF DATABASES

Every column in a WHERE clause, JOIN condition, or ORDER BY should have an index.

Basic index creation:

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Unique index (prevents duplicates + faster lookups)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Full text index (for searching text)
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);

-- In Laravel migrations:
Schema::table('users', function (Blueprint $table) {
    $table->index('email');                    // Simple index
    $table->unique('email');                   // Unique index
    $table->index(['user_id', 'status']);      // Composite index
    $table->fullText('content');               // Full text index
});

🔬 The EXPLAIN Command: Your Best Friend

BEFORE ANY QUERY GOES TO PRODUCTION, RUN EXPLAIN

EXPLAIN tells you exactly how MySQL will execute your query — before it runs.

👎 BAD: type = ALL (Full Table Scan)

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 1000000 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 🔴 type = ALL → Full table scan! 1,000,000 rows examined!

Problem: No index on email. MySQL scans every single row.

👍 GOOD: type = ref or range (Index Scan)

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com'; +----+-------------+-------+------+------------------+------------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+------------------+------------------+---------+-------+------+-------+ | 1 | SIMPLE | users | ref | idx_users_email | idx_users_email | 1022 | const | 1 | NULL | +----+-------------+-------+------+------------------+------------------+---------+-------+------+-------+ ✅ type = ref → Index lookup! Only 1 row examined!

Solution: Added INDEX idx_users_email ON users(email). MySQL now uses binary search.

What to look for in EXPLAIN output:

ColumnBad ValueGood ValueMeaning
type ALL, index ref, eq_ref, range, const ALL = full table scan (disaster). ref/range = index used (good).
key NULL index_name NULL means no index used. Any value means index was used.
rows Large number (100k+) Small number (1-100) Estimated rows examined. Lower is better.
Extra "Using filesort", "Using temporary" "Using index", NULL Filesort/temporary = bad (needs index optimization).

📐 Composite Index: Column Order Matters

THE ORDER TRAP

A composite index on (user_id, created_at) is NOT the same as (created_at, user_id). The order determines which queries can use the index.

│ │ COMPOSITE INDEX: (user_id, created_at) │ ═══════════════════════════════════════════════════════════════════ │ │ Index structure (simplified): │ ┌─────────────────────────────────────────────────────────────────┐ │ │ user_id=1, created_at=2024-01-01 │ │ │ user_id=1, created_at=2024-01-02 │ │ │ user_id=1, created_at=2024-01-03 │ │ │ ... │ │ │ user_id=2, created_at=2024-01-01 │ │ │ user_id=2, created_at=2024-01-02 │ │ │ ... │ │ └─────────────────────────────────────────────────────────────────┘ │ │ Queries that can use this index: │ ✅ WHERE user_id = 123 (uses leftmost column) │ ✅ WHERE user_id = 123 AND created_at > '2024-01-01' (uses both) │ ❌ WHERE created_at > '2024-01-01' (cannot use — no user_id) │
THE RULE OF LEFT-MOST PREFIX

MySQL can use a composite index only when the query filters on the leftmost column(s). Put the most selective column first.

Examples:

IndexQueryCan use index?
(user_id, status, created_at) WHERE user_id = 123 ✅ Yes (uses leftmost)
(user_id, status, created_at) WHERE user_id = 123 AND status = 'active' ✅ Yes (uses first two)
(user_id, status, created_at) WHERE user_id = 123 AND created_at > '2024-01-01' ✅ Partial (skips status, less efficient)
(user_id, status, created_at) WHERE status = 'active' ❌ No (user_id is missing)
(user_id, status, created_at) WHERE created_at > '2024-01-01' ❌ No (leftmost missing)

⚖️ The Trade-Off: Indexes Speed Up Reads, Slow Down Writes

Benefits (Reads)Costs (Writes)
SELECT queries 10-50,000x faster —
INSERT — 10-50% slower (must update indexes)
UPDATE — Slower if indexed columns change
DELETE — Must update indexes
JOIN operations Much faster with indexes —
Disk space — 5-10% additional per index
THE BALANCE

Don't index everything. Each index makes writes slower and uses disk space. Index only the columns you actually query on.
For a typical Laravel app with 90% reads and 10% writes, indexes are almost always worth it.

🔎 How to Find Missing Indexes

METHOD 1: MySQL Slow Query Log 
# Enable slow query log (my.cnf)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # Log queries taking > 2 seconds

Then analyze: pt-query-digest /var/log/mysql/slow.log

METHOD 2: Laravel Debugbar

Install barryvdh/laravel-debugbar. It shows:

METHOD 3: MySQL's Missing Index Feature 
-- MySQL can suggest missing indexes
SELECT * FROM users WHERE email = 'john@example.com'\G

-- Look at the 'Extra' column for index suggestions

📝 Topic 9 Summary: Database Indexing & EXPLAIN

Query TypeWithout IndexWith IndexHow to Identify
Equality (WHERE col = value) Full table scan Index lookup Simple index on column
Range (WHERE col > value) Full table scan Index range scan Index on column (range works)
JOIN (ON t1.col = t2.col) Nested loops / full scan Index join Index on both sides of JOIN
ORDER BY Filesort (temp table) Index scan Index on ORDER BY column(s)
GROUP BY Temporary table Index group by Index on GROUP BY column(s)
📌 THE RULE: Run EXPLAIN on every query. If type = ALL, add an index. If Extra = "Using filesort" or "Using temporary", add a composite index. Your database will thank you.

The difference between a 5-second query and a 0.001-second query is almost always a missing index. Don't be that developer.
NEXT TOPIC PREVIEW

Topic 10: Read/Write Splitting (Master/Replica) — How to scale your database horizontally. Send writes to master, reads to replicas. Double your capacity without changing a line of application code.