Database Indexes: Why Your Queries Are Slow

Your app is slow. Users complain. You check the database.

Query takes 5 seconds. 100,000 rows scanned.

One index later: 0.02 seconds.

Here's how.

The Problem

SELECT * FROM orders 
WHERE user_id = 123 
AND status = 'pending';

Without indexes, database scans every row. 1 million orders? Scans all 1 million.

With indexes: Jumps straight to relevant rows. Scans maybe 10.

Basic Index (Laravel)

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

Now queries filtering by user_id are instant.

Composite Indexes

Query uses multiple columns:

SELECT * FROM orders 
WHERE user_id = 123 
AND status = 'pending';

Single column indexes help, but composite is better:

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

Order matters:

• index(['user_id', 'status']) - Fast for user_id, fast for user_id + status
• index(['status', 'user_id']) - Fast for status, fast for status + user_id

Use the column you filter most first.

Real Example

Client: E-commerce site, order history page taking 8 seconds.

Query:

SELECT * FROM orders 
WHERE user_id = ? 
AND created_at > ? 
ORDER BY created_at DESC 
LIMIT 20;

Before indexes:

• Execution time: 8.2 seconds
• Rows scanned: 2.5 million
• Users complaining

Added index:

$table->index(['user_id', 'created_at']);

After:

• Execution time: 0.03 seconds
• Rows scanned: 47
• Users happy

When to Add Indexes

Add indexes for:

1. Foreign keys

$table->foreignId('user_id')->constrained();
// Laravel adds index automatically

2. WHERE clauses

WHERE status = 'active'  -- Index status
WHERE email = 'user@example.com'  -- Index email

3. ORDER BY columns

ORDER BY created_at DESC  -- Index created_at

4. JOIN columns

JOIN order_items ON orders.id = order_items.order_id
-- Index order_items.order_id

When NOT to Add Indexes

Skip indexes for:

1. Small tables

// Table with 100 rows? No index needed
// Database scans 100 rows instantly

2. Columns with few unique values

// gender column (male/female)? Bad index
// status column (active/inactive)? Bad index
// Only 2 values, index doesn't help much

3. Columns rarely queried

// If you never filter by 'notes', don't index it

4. Write-heavy tables

// Logs table with 10,000 inserts/second?
// Too many indexes slow down writes

Index Types

Unique Index

$table->unique('email');

Enforces uniqueness + speeds up queries.

Full-Text Index

$table->fullText('description');

For searching text:

SELECT * FROM products 
WHERE MATCH(description) AGAINST('laptop');

Partial Index (PostgreSQL)

DB::statement('
    CREATE INDEX active_users_idx 
    ON users (email) 
    WHERE status = "active"
');

Indexes only active users. Smaller, faster.

Finding Missing Indexes

Laravel Debugbar

Shows slow queries:

composer require barryvdh/laravel-debugbar --dev

Look for queries taking > 100ms.

EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

Shows if index is used:

type: ALL  -- Bad! Full table scan
type: ref  -- Good! Using index

Slow Query Log (MySQL)

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Logs queries taking > 1 second.

Common Mistakes

Mistake 1: Too Many Indexes

// Don't do this
$table->index('user_id');
$table->index('status');
$table->index('created_at');
$table->index(['user_id', 'status']);
$table->index(['user_id', 'created_at']);
$table->index(['status', 'created_at']);
$table->index(['user_id', 'status', 'created_at']);

Every index slows down INSERT/UPDATE. Add only what you need.

Mistake 2: Wrong Column Order

// Query: WHERE user_id = ? AND status = ?
$table->index(['status', 'user_id']);  // Wrong order!
$table->index(['user_id', 'status']);  // Correct!

Most selective column first.

Mistake 3: Indexing Everything

// Don't index columns you never query
$table->index('notes');  // Never used in WHERE
$table->index('description');  // Never filtered

Wastes space, slows writes.

Monitoring Index Usage

Check unused indexes (PostgreSQL):

SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Check unused indexes (MySQL):

SELECT * FROM sys.schema_unused_indexes;

Remove unused indexes.

Real Project Results

Project 1: School Management System

Added 3 indexes:

$table->index(['student_id', 'semester']);
$table->index(['teacher_id', 'subject_id']);
$table->index('enrollment_date');

Results:

• Dashboard load: 4.2s → 0.3s
• Report generation: 12s → 1.1s
• Database CPU: 75% → 25%

Project 2: E-commerce API

Added 5 indexes:

$table->index(['user_id', 'created_at']);
$table->index(['product_id', 'status']);
$table->index('order_number');
$table->index(['category_id', 'is_active']);
$table->fullText('product_name');

Results:

• API response time: 850ms → 120ms
• Search queries: 3.5s → 0.2s
• Server costs: Reduced 30%

Best Practices

1. Index foreign keys - Always
2. Index WHERE columns - If queried often
3. Composite indexes - For multi-column queries
4. Monitor slow queries - Find missing indexes
5. Remove unused indexes - Keep it lean
6. Test before production - Verify performance gain

Bottom Line

Indexes are the easiest performance win.

One index can turn a 5-second query into 0.05 seconds.

But don't over-index. Add what you need, monitor, adjust.

---

Need database optimization?

We optimize databases for Nigerian businesses. Slow queries, performance tuning, scaling.

📞 WhatsApp: +234 708 711 0468
📧 info@raspibtech.com
📍 Lagos Island

Related:

• Laravel 12 Features
• API Rate Limiting