Query Optimization

Cache membuat pembacaan cepat, tetapi query yang boros tetap membebani database di setiap cache miss — dan makin banyak data, makin sering miss itu terjadi. Optimasi query karena itu selalu menjadi fondasi performa sebelum menumpuk lapisan cache di atasnya. Prinsip kerjanya sederhana: ukur dulu, temukan biaya sebenarnya, baru ubah. Pada chapter ini kita akan menghitung query per request, membasmi pola N+1, membaca rencana eksekusi, memanfaatkan indeks, dan mengganti paginasi OFFSET dengan keyset. Semua contoh memakai SQLite :memory: agar dapat dijalankan langsung; prinsip yang sama berlaku pada MySQL dan PostgreSQL dengan EXPLAIN sebagai padanan EXPLAIN QUERY PLAN.

Menghitung Query per Request #

Angka query per halaman adalah metrik termurah untuk mendeteksi query boros: satu angka, tanpa perlu alat profiling. Bungkus koneksi PDO dengan class yang menghitung setiap eksekusi:

<?php
class CountingConnection
{
    private int $jumlahQuery = 0;

    public function __construct(private readonly PDO $pdo) {}

    public function query(string $sql): PDOStatement
    {
        $this->jumlahQuery++;
        return $this->pdo->query($sql);
    }

    public function jumlahQuery(): int
    {
        return $this->jumlahQuery;
    }
}

$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE pengguna (id INTEGER PRIMARY KEY, nama TEXT)");
$pdo->exec("INSERT INTO pengguna (nama) VALUES ('Budi'), ('Siti'), ('Anton')");

$db = new CountingConnection($pdo);

$daftar = $db->query("SELECT id, nama FROM pengguna")->fetchAll(PDO::FETCH_ASSOC);
echo "Baris dimuat: " . count($daftar) . "\n";
echo "Jumlah query: " . $db->jumlahQuery() . "\n";

Output yang Dihasilkan:

Baris dimuat: 3
Jumlah query: 1

Class CountingConnection menyimpan satu PDO di property dan menaikkan penghitung setiap kali query() dipanggil, lalu mendelegasikan eksekusi ke PDO asli. Penghitung itu dibaca kembali setelah seluruh pekerjaan selesai. Pada aplikasi nyata, pembungkus seperti ini cukup mencatat ke query log dan melaporkan totalnya di akhir request; angka yang sehat untuk halaman daftar biasanya satu digit — bila satu halaman menghasilkan puluhan query, hampir pasti ada pola yang perlu diperbaiki.

Masalah N+1 #

Pembunuh angka query paling terkenal adalah pola N+1: satu query untuk mengambil daftar, lalu satu query tambahan untuk setiap barisnya. Mari kita bangun pola ini secara sengaja menggunakan dua tabel yang saling terkait:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE pengguna (id INTEGER PRIMARY KEY, nama TEXT)");
$pdo->exec("CREATE TABLE pesanan (id INTEGER PRIMARY KEY, pengguna_id INTEGER, total INTEGER)");
$pdo->exec("INSERT INTO pengguna (nama) VALUES ('Budi'), ('Siti'), ('Anton')");
$pdo->exec("INSERT INTO pesanan (pengguna_id, total) VALUES (1, 150000), (1, 220000), (2, 75000), (3, 310000), (3, 52000)");

$jumlahQuery = 0;

// Query ke-1: ambil daftar pengguna
$pengguna = $pdo->query("SELECT id, nama FROM pengguna")->fetchAll(PDO::FETCH_ASSOC);
$jumlahQuery++;

// Query ke-2 sampai ke-(N+1): satu query tambahan per pengguna
foreach ($pengguna as $p) {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM pesanan WHERE pengguna_id = ?");
    $stmt->execute([$p["id"]]);
    $p["jumlah_pesanan"] = (int) $stmt->fetchColumn();
    $jumlahQuery++;
}

echo "Total query: $jumlahQuery\n";

Output yang Dihasilkan:

Total query: 4

Query pertama mengambil tiga pengguna, kemudian loop menjalankan satu query COUNT(*) untuk tiap pengguna — total empat query untuk data yang sesungguhnya sederhana. Dengan tiga pengguna pola ini belum terasa; dengan 200 pengguna, halaman yang sama mengeksekusi 201 query, dan setiap query menambah satu kali bolak-balik ke server database. Pola ini juga sering muncul secara diam-diam lewat lazy loading di ORM: setiap akses relasi memicu query baru tanpa terlihat di kode loop Anda — itulah mengapa menghitung query tetap perlu meskipun memakai ORM.

Solusi: JOIN atau WHERE IN #

Perbaikan N+1 selalu berarah sama: gabungkan pengambilan data sehingga jumlah query tidak lagi bergantung pada jumlah baris. Untuk agregat per baris, LEFT JOIN dengan GROUP BY menyelesaikannya dalam satu query:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE pengguna (id INTEGER PRIMARY KEY, nama TEXT)");
$pdo->exec("CREATE TABLE pesanan (id INTEGER PRIMARY KEY, pengguna_id INTEGER, total INTEGER)");
$pdo->exec("INSERT INTO pengguna (nama) VALUES ('Budi'), ('Siti'), ('Anton')");
$pdo->exec("INSERT INTO pesanan (pengguna_id, total) VALUES (1, 150000), (1, 220000), (2, 75000), (3, 310000), (3, 52000)");

// Satu query dengan JOIN: seluruh data gabungan diambil sekaligus
$stmt = $pdo->prepare(
    "SELECT p.id, p.nama, COUNT(o.id) AS jumlah_pesanan
     FROM pengguna p
     LEFT JOIN pesanan o ON o.pengguna_id = p.id
     GROUP BY p.id, p.nama
     ORDER BY p.id"
);
$stmt->execute();

echo "Total query: 1\n";
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo "{$row["nama"]}: {$row["jumlah_pesanan"]} pesanan\n";
}

Output yang Dihasilkan:

Total query: 1
Budi: 2 pesanan
Siti: 1 pesanan
Anton: 2 pesanan

LEFT JOIN memasangkan setiap pengguna dengan pesanannya, GROUP BY p.id, p.nama mengelompokkan hasil per pengguna, dan COUNT(o.id) menghitung pesanan di tiap kelompok. Total query turun dari empat menjadi satu, dan hasilnya identik. Alternatif lain adalah dua query dengan WHERE IN:

SELECT id, pengguna_id FROM pesanan WHERE pengguna_id IN (1, 2, 3)

Pola WHERE IN mengambil semua baris pesanan sekali lalu mengelompokkannya di PHP — berguna ketika agregasi terlalu rumit untuk SQL atau saat join lintas database tidak mungkin dilakukan. Pilih JOIN bila murni urusan satu database; pilih WHERE IN bila logika pengelompokkannya lebih alami ditulis di PHP.

Indeks: dari SCAN ke SEARCH #

Setelah jumlah query dipangkas, giliran biaya per query yang diperiksa. EXPLAIN QUERY PLAN di SQLite menunjukkan rencana eksekusi: bagaimana engine mencari data Anda. Mari kita lihat sebelum dan sesudah indeks:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE pesanan (id INTEGER PRIMARY KEY, email TEXT, total INTEGER)");
$pdo->exec("INSERT INTO pesanan (email, total) VALUES
    ('[email protected]', 150000),
    ('[email protected]', 75000),
    ('[email protected]', 310000),
    ('[email protected]', 98000)");

function rencanaQuery(PDO $pdo, string $sql): string
{
    $baris = $pdo->query("EXPLAIN QUERY PLAN " . $sql)->fetchAll(PDO::FETCH_ASSOC);
    return $baris[0]["detail"];
}

// Kolom total TIDAK ada di indeks → indeks hanya membantu pencarian
$sql = "SELECT total FROM pesanan WHERE email = '[email protected]'";

echo "Sebelum indeks : " . rencanaQuery($pdo, $sql) . "\n";

$pdo->exec("CREATE INDEX idx_pesanan_email ON pesanan(email)");

echo "Setelah indeks : " . rencanaQuery($pdo, $sql) . "\n";

Output yang Dihasilkan:

Sebelum indeks : SCAN pesanan
Setelah indeks : SEARCH pesanan USING INDEX idx_pesanan_email (email=?)

rencanaQuery() menjalankan EXPLAIN QUERY PLAN di depan query asli dan mengambil kolom detail dari baris pertama hasilnya. Sebelum ada indeks, engine melakukan SCAN pesanan: memeriksa setiap baris satu per satu — dengan empat baris tidak terasa, dengan empat juta baris berarti empat juta pemeriksaan untuk satu hasil. Setelah CREATE INDEX pada kolom email, engine beralih ke SEARCH ... USING INDEX: melompat langsung ke baris yang cocok lewat struktur B-tree, yang biayanya tumbuh mengikuti logaritma jumlah baris. Buat indeks pada kolom yang sering muncul di WHERE, JOIN, dan ORDER BY; ingat bahwa indeks membayar dirinya dengan ruang penyimpanan dan penulisan yang sedikit lebih lambat, jadi buat untuk pola akses yang benar-benar sering dipakai.

Covering Index #

Indeks pada satu kolom mempercepat pencarian, tetapi bila query juga membutuhkan kolom lain, engine tetap harus membuka baris aslinya di tabel. Indeks yang memuat semua kolom yang dibutuhkan query disebut covering index — engine menyelesaikan query sepenuhnya dari dalam indeks tanpa menyentuh tabel:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE pesanan (id INTEGER PRIMARY KEY, email TEXT, total INTEGER)");
$pdo->exec("INSERT INTO pesanan (email, total) VALUES ('[email protected]', 150000), ('[email protected]', 75000)");

// Indeks gabungan: dipakai untuk filter email SEKALIGUS menyediakan kolom total
$pdo->exec("CREATE INDEX idx_email_total ON pesanan(email, total)");

function rencana(PDO $pdo, string $sql): string
{
    return $pdo->query("EXPLAIN QUERY PLAN " . $sql)
        ->fetchAll(PDO::FETCH_ASSOC)[0]["detail"];
}

// Query hanya butuh kolom yang sudah ada di dalam indeks
$sql = "SELECT total FROM pesanan WHERE email = '[email protected]'";
echo rencana($pdo, $sql) . "\n";

Output yang Dihasilkan:

SEARCH pesanan USING COVERING INDEX idx_email_total (email=?)

Indeks idx_email_total dibuat pada pasangan kolom (email, total) — sama seperti indeks di bagian sebelumnya, tetapi kini query SELECT total ... WHERE email = ... menemukan kedua kolom yang dibutuhkannya di dalam indeks. Rencana eksekusi menandainya dengan USING COVERING INDEX. Triknya efektif untuk query Dashboard yang menyaring dan menjumlahkan kolom yang sama berulang kali; batasannya, setiap kolom tambahan membuat indeks makin lebar dan makan tempat, jadi bangun covering index hanya untuk query yang benar-benar panas.

Pilih Hanya Kolom yang Dibutuhkan #

Kebiasaan SELECT * terasa praktis, tetapi pada tabel dengan kolom besar ia menyeret seluruh isi yang tidak dibutuhkan — dari database, melalui jaringan, hingga memori PHP. Perhatikan perbandingan berikut:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE artikel (
    id INTEGER PRIMARY KEY,
    judul TEXT,
    isi TEXT,
    penulis TEXT,
    dibuat_pada TEXT,
    dilihat INTEGER DEFAULT 0
)");
$pdo->exec("INSERT INTO artikel (judul, isi, penulis, dibuat_pada, dilihat)
    VALUES ('Belajar Cache', 'Isi artikel yang panjang ...', 'Budi', '2026-01-15', 1200)");

$selectSemua = $pdo->query("SELECT * FROM artikel")->fetch(PDO::FETCH_ASSOC);
$selectDua = $pdo->query("SELECT id, judul FROM artikel")->fetch(PDO::FETCH_ASSOC);

echo "SELECT * memuat " . count($selectSemua) . " kolom\n";
echo "SELECT eksplisit memuat " . count($selectDua) . " kolom\n";

Output yang Dihasilkan:

SELECT * memuat 6 kolom
SELECT eksplisit memuat 2 kolom

Kedua query membaca tabel yang sama, tetapi SELECT * menghasilkan enam kolom sementara query eksplisit hanya dua. Untuk satu baris perbedaannya kecil; untuk daftar 50 artikel di halaman indeks, SELECT * memuat 50 salinan kolom isi yang panjangnya bisa ribuan karakter — padahal halaman hanya menampilkan judul. Sebutkan kolom secara eksplisit: kode menjadi terdokumentasi sendiri, dan perubahan struktur tabel tidak lagi mengubah bentuk data yang diterima kode Anda secara diam-diam.

Paginasi dengan Keyset #

Paginasi klasik LIMIT n OFFSET m diam-diam menjadi mahal pada halaman yang dalam: untuk melompati 100.000 baris, engine tetap harus membaca dan membuang 100.000 baris tersebut lebih dulu. Paginasi keyset (disebut juga seek method) memakai nilai baris terakhir halaman sebelumnya sebagai kursor:

<?php
$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE pengguna (id INTEGER PRIMARY KEY, nama TEXT)");
$pdo->exec("INSERT INTO pengguna (id, nama) VALUES
    (1, 'Budi'), (2, 'Siti'), (3, 'Anton'), (4, 'Dewi'),
    (5, 'Rina'), (6, 'Fajar'), (7, 'Gita'), (8, 'Hadi')");

$ukuranHalaman = 3;

// Halaman pertama: tiga baris teratas
$halaman1 = $pdo->query("SELECT id, nama FROM pengguna ORDER BY id LIMIT $ukuranHalaman")
    ->fetchAll(PDO::FETCH_ASSOC);

// Kursor = nilai id terakhir halaman sebelumnya
$kursor = (int) end($halaman1)["id"];

// Halaman kedua dengan keyset: lanjut dari kursor, bukan OFFSET
$stmt = $pdo->prepare("SELECT id, nama FROM pengguna WHERE id > ? ORDER BY id LIMIT ?");
$stmt->execute([$kursor, $ukuranHalaman]);

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo "{$row["id"]} {$row["nama"]}\n";
}

Output yang Dihasilkan:

4 Dewi
5 Rina
6 Fajar

Halaman pertama diambil seperti biasa, lalu end($halaman1)["id"] mengambil id terakhirnya sebagai kursor. Halaman kedua memakai WHERE id > ? — engine melompat langsung ke posisi kursor lewat indeks primary key, seberapa pun dalamnya halaman. Bonus penting: hasil keyset stabil saat ada baris baru disisipkan di tengah penelusuran, sedangkan OFFSET bisa menampilkan baris yang sama dua kali atau melompati baris. Syaratnya satu: kolom kursor harus unik dan terurut — id auto-increment adalah kandidat standar.

Ringkasan #

Kita telah menempuh alur optimasi yang lengkap: hitung query per request untuk menemukan pemborosan, gabungkan query N+1 dengan JOIN atau WHERE IN, baca rencana eksekusi dengan EXPLAIN QUERY PLAN, bangun indeks (dan covering index) pada kolom pencarian, pilih kolom secara eksplisit, dan paginasi dengan keyset. Query yang efisien memangkas waktu tunggu, tetapi ada satu sumber daya lain yang sama mudahnya habis: memori PHP. Chapter Reducing Memory Usage berikutnya membahas generator, fetch bertahap, dan streaming berkas untuk menekannya.

Referensi #

• SQLite: EXPLAIN QUERY PLAN
• SQLite: The SQLite Query Planner
• PHP: PDO — Prepared Statements
• PHP: PDO — Manual