Backend Database Performance

When to use this skill

• Slow queries
• N+1 problems
• Large datasets
• Production optimization

Workflow

• Profile queries
• Add indexes
• Prevent N+1
• Use eager loading
• Cache results

Instructions

N+1 Problem

<?php

// ❌ Bad: N+1 queries
$users = $userModel->findAll();
foreach ($users as $user) {
    $user->orders; // Separate query per user
}

// ✅ Good: Single query with join
$users = $this->db->table('users u')
    ->select('u.*, GROUP_CONCAT(o.id) as order_ids')
    ->join('orders o', 'o.user_id = u.id', 'left')
    ->groupBy('u.id')
    ->get()
    ->getResult();

Indexing

<?php

$this->forge->addField([
    'email' => ['type' => 'VARCHAR', 'constraint' => 255]
]);
$this->forge->addKey('email'); // Index
$this->forge->addUniqueKey('email'); // Unique index

Query Profiling

<?php

$db = \Config\Database::connect();
$db->query("SELECT * FROM users WHERE status = 'active'");
$db->showLastQuery(); // See exact query

Resources

• Index frequently queried columns
• Avoid SELECT *
• Use LIMIT for pagination
• Cache expensive queries