Source code aplikasi, template web, dan ebook. Unduh dari akun setelah bayar. Lihat produk

Blog / Performance, Caching & Scalability / Cara Mengoptimalkan Query MySQ...
Performance, Caching & Scalability

Cara Mengoptimalkan Query MySQL Laravel Menggunakan Index dan EXPLAIN

AD

Admin HijrahKreativa

Penulis

19 Oct 2026
13 min read
12 views

Menemukan query lambat dengan slow query log dan Laravel, membaca hasil EXPLAIN dan EXPLAIN ANALYZE, merancang composite index dengan urutan kolom yang benar, mengenali pola query yang membuat index tidak terpakai, mengganti pagination OFFSET dengan keyset, serta menambah index di tabel besar tanpa mengunci aplikasi.

Overview

Tabel iuran di aplikasi RW tumbuh sekitar 4.000 baris per bulan per RW. Setelah dipakai 30 RW selama beberapa tahun, isinya lebih dari satu juta baris. Halaman "Tunggakan" yang dulu muncul dalam 50 ms kini butuh 3 detik, dan dasbor bendahara sering timeout.

Kode Laravel-nya tidak berubah. Yang berubah adalah volume data, dan query yang tidak didukung index membaca seluruh tabel setiap kali dijalankan. Dengan 1.000 baris itu tidak terasa; dengan 1.000.000 baris, terasa sekali.

Hasil akhir:

  • slow query log MySQL dan deteksi query lambat dari Laravel,
  • kemampuan membaca EXPLAIN dan EXPLAIN ANALYZE,
  • aturan merancang composite index (kesamaan dulu, lalu rentang/urutan),
  • daftar pola query yang mematikan index dan perbaikannya,
  • keyset pagination untuk tabel besar,
  • menambah index di production dengan aman, menemukan index yang tidak terpakai,
  • test yang memastikan query penting tetap memakai index.

Prerequisites

Komponen Versi
PHP 8.3+ (disarankan 8.4)
Laravel 13.x
MySQL 8.4 LTS (berlaku juga untuk 8.0; MariaDB punya sintaks EXPLAIN sedikit berbeda)
Data uji Volume mendekati production (gunakan factory/seeder untuk mengisi ratusan ribu baris)

Struktur tabel contoh:

Schema::create('iuran', function (Blueprint $table) {
    $table->id();
    $table->foreignId('keluarga_id')->constrained('keluarga');
    $table->foreignId('jenis_iuran_id')->constrained('jenis_iuran');
    $table->foreignId('rukun_tetangga_id')->constrained('rukun_tetangga');
    $table->char('periode', 7);                    // "2026-10"
    $table->unsignedInteger('nominal');
    $table->string('status', 20);                  // belum_bayar | lunas | dibatalkan
    $table->date('jatuh_tempo');
    $table->timestamp('dilunasi_pada')->nullable();
    $table->timestamps();

    $table->unique(['keluarga_id', 'jenis_iuran_id', 'periode']);
});

Step-by-step Tutorial

Langkah 1: Temukan Query yang Lambat

Slow query log MySQL mencatat query yang melebihi batas waktu:

# /etc/mysql/mysql.conf.d/mysqld.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0     # 1 bisa sangat berisik; aktifkan sementara saat investigasi
min_examined_row_limit = 1000

Ringkas lognya dengan mysqldumpslow atau pt-query-digest (Percona Toolkit):

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
sudo pt-query-digest /var/log/mysql/slow.log | less

pt-query-digest mengelompokkan query yang sama dengan parameter berbeda dan mengurutkannya berdasarkan total waktu. Query yang 80 ms tetapi dijalankan 50.000 kali sehari sering lebih penting daripada query 5 detik yang dijalankan sekali.

Dari sisi Laravel:

// app/Providers/AppServiceProvider.php
DB::listen(function ($query) {
    if ($query->time > 300) {
        Log::channel('performa')->warning('db.query_lambat', [
            'sql' => $query->sql,
            'ms'  => $query->time,
            'url' => request()?->path(),
        ]);
    }
});

Skema sys MySQL juga menyediakan ringkasan siap pakai:

SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC
LIMIT 10;

Langkah 2: Dapatkan SQL dan Jalankan EXPLAIN dari Laravel

$query = Iuran::query()
    ->where('rukun_tetangga_id', 4)
    ->where('status', 'belum_bayar')
    ->where('jatuh_tempo', '<', now()->toDateString())
    ->orderBy('jatuh_tempo');

$query->toRawSql();     // SQL lengkap dengan nilai binding, untuk disalin ke klien MySQL
$query->explain()->dd();

Atau langsung di MySQL:

EXPLAIN
SELECT * FROM iuran
WHERE rukun_tetangga_id = 4 AND status = 'belum_bayar' AND jatuh_tempo < '2026-10-19'
ORDER BY jatuh_tempo;

Hasil sebelum ada index yang tepat:

+----+-------+------+---------------------------+---------------------------+--------+----------+-----------------------------+
| id | table | type | possible_keys             | key                       | rows   | filtered | Extra                       |
+----+-------+------+---------------------------+---------------------------+--------+----------+-----------------------------+
|  1 | iuran | ref  | iuran_rukun_tetangga_id_… | iuran_rukun_tetangga_id_… | 41250  |     3.70 | Using where; Using filesort |
+----+-------+------+---------------------------+---------------------------+--------+----------+-----------------------------+

Langkah 3: Membaca Kolom EXPLAIN

Kolom Yang dicari
type Cara mengakses tabel. Dari terbaik ke terburuk: const > eq_ref > ref > range > index > ALL. ALL = membaca seluruh tabel. index = membaca seluruh index (lebih ringan dari ALL, tetapi tetap seluruhnya).
possible_keys Index yang mungkin dipakai. Kosong = tidak ada index yang cocok.
key Index yang benar-benar dipilih.
key_len Berapa byte index yang dipakai; menunjukkan berapa kolom composite index yang terpakai.
rows Perkiraan baris yang diperiksa. Bandingkan dengan jumlah baris yang dikembalikan.
filtered Persentase baris yang lolos kondisi setelah dibaca. Rendah (misalnya 3,7%) = banyak baris dibaca sia-sia.
Extra Using filesort (pengurutan tambahan), Using temporary (tabel sementara, biasa pada GROUP BY/DISTINCT), Using index (covering index, sangat baik), Using index condition (index condition pushdown).

Contoh di atas: MySQL memakai index rukun_tetangga_id (dibuat otomatis oleh foreign key), membaca sekitar 41.250 baris milik RT 4, membuang 96% di antaranya, lalu mengurutkan sisanya. Itu pemborosan.

Langkah 4: EXPLAIN ANALYZE untuk Angka Nyata

EXPLAIN hanya perkiraan. EXPLAIN ANALYZE (MySQL 8.0.18+) benar-benar menjalankan query dan melaporkan waktu serta jumlah baris aktual per tahap:

EXPLAIN ANALYZE
SELECT * FROM iuran
WHERE rukun_tetangga_id = 4 AND status = 'belum_bayar' AND jatuh_tempo < '2026-10-19'
ORDER BY jatuh_tempo;
-> Sort: iuran.jatuh_tempo  (cost=... rows=1526) (actual time=212..214 rows=1490 loops=1)
    -> Filter: ((iuran.`status` = 'belum_bayar') and (iuran.jatuh_tempo < DATE'2026-10-19'))
                (actual time=0.9..208 rows=1490 loops=1)
        -> Index lookup on iuran using iuran_rukun_tetangga_id_foreign (rukun_tetangga_id=4)
                (actual time=0.8..195 rows=41250 loops=1)

Baca dari bawah ke atas: 41.250 baris dibaca lewat index, 1.490 lolos filter, lalu diurutkan. Jangan jalankan EXPLAIN ANALYZE untuk UPDATE/DELETE di production, karena query benar-benar dieksekusi.

Langkah 5: Merancang Composite Index

Aturan urutan kolom dalam composite index:

  1. Kolom dengan kondisi kesamaan (=, IN) lebih dulu.
  2. Lalu satu kolom rentang (<, >, BETWEEN, LIKE 'abc%') atau kolom pengurutan (ORDER BY).
  3. Kolom setelah kolom rentang tidak bisa dipakai untuk pencarian (hanya untuk penyaringan di dalam index).

Index B-tree bekerja dengan leftmost prefix: index (a, b, c) bisa dipakai untuk kondisi a, a + b, atau a + b + c, tetapi tidak untuk b saja atau c saja.

Untuk query tunggakan:

// database/migrations/2026_10_19_000001_tambah_index_tunggakan_iuran.php
public function up(): void
{
    Schema::table('iuran', function (Blueprint $table) {
        // kesamaan: rukun_tetangga_id, status → rentang & urutan: jatuh_tempo
        $table->index(['rukun_tetangga_id', 'status', 'jatuh_tempo'], 'iuran_rt_status_tempo_idx');
    });
}

public function down(): void
{
    Schema::table('iuran', fn (Blueprint $table) => $table->dropIndex('iuran_rt_status_tempo_idx'));
}

Hasil setelah index:

| type  | key                       | rows | filtered | Extra                 |
| range | iuran_rt_status_tempo_idx | 1490 |   100.00 | Using index condition |

Baris yang diperiksa turun dari 41.250 menjadi 1.490, dan Using filesort hilang karena data sudah terurut sesuai index.

Index lama rukun_tetangga_id (dari foreign key) sekarang tercakup oleh prefix index baru. MySQL butuh index yang diawali kolom foreign key; index komposit baru memenuhi syarat itu, sehingga index tunggal yang lama secara teori bisa dihapus. Lakukan hanya setelah memastikan tidak ada query lain yang bergantung padanya (lihat Langkah 10).

Covering index. Bila semua kolom yang dibutuhkan query ada di index, MySQL tidak perlu membaca baris tabel sama sekali (Extra: Using index):

// Hanya butuh hitungan per status untuk satu periode
Iuran::where('periode', '2026-10')->groupBy('status')->selectRaw('status, COUNT(*) total, SUM(nominal) jumlah')->get();

// Index covering: (periode, status, nominal)
$table->index(['periode', 'status', 'nominal']);

Langkah 6: Pola Query yang Mematikan Index

Pola Contoh Masalah Perbaikan
Fungsi pada kolom whereYear('created_at', 2026) → YEAR(created_at) = 2026 Index created_at tidak bisa dipakai whereBetween('created_at', ['2026-01-01', '2027-01-01']) (ingat batas atas eksklusif)
Fungsi tanggal whereDate('dilunasi_pada', $hari) Sama where('dilunasi_pada', '>=', $hari)->where('dilunasi_pada', '<', $besok)
Wildcard di depan like '%budi%' Tidak bisa memakai B-tree Full-text index, atau like 'budi%' bila cukup
Konversi tipe implisit where('nik', 3201...) pada kolom VARCHAR Perbandingan angka vs string mengabaikan index Kirim sebagai string: where('nik', (string) $nik)
OR antar-kolom berbeda where('nama', $q)->orWhere('nik', $q) Sering berujung ALL Index terpisah tiap kolom (index merge) atau UNION
Negasi where('status', '!=', 'lunas') Rentang luas, index kurang efektif Tulis sebagai whereIn('status', ['belum_bayar', 'dibatalkan'])
ORDER BY kolom tanpa index + LIMIT latest('dilunasi_pada')->limit(10) Mengurutkan seluruh hasil sebelum mengambil 10 Index yang berakhir dengan kolom urutan
Kolom kolasi berbeda di JOIN utf8mb4_unicode_ci vs utf8mb4_0900_ai_ci Index di sisi join diabaikan Samakan kolasi kolom
whereHas pada relasi besar Keluarga::whereHas('iuran', ...) Subquery EXISTS per baris Index pada foreign key + kolom kondisi; atau whereIn dengan subquery/join

Contoh perbaikan whereYear untuk laporan tahunan:

// ❌
TransaksiKas::whereYear('tanggal', 2026)->sum('nominal');

// ✅ memakai index (tanggal)
TransaksiKas::where('tanggal', '>=', '2026-01-01')
    ->where('tanggal', '<', '2027-01-01')
    ->sum('nominal');

Langkah 7: Pencarian Teks dengan Full-Text Index

Untuk pencarian nama warga atau isi pengumuman, LIKE '%kata%' pada tabel besar selalu membaca semua baris. Gunakan full-text index:

Schema::table('pengumuman', function (Blueprint $table) {
    $table->fullText(['judul', 'isi']);
});
Pengumuman::whereFullText(['judul', 'isi'], $kata)->latest('terbit_pada')->paginate();

// Mode boolean untuk prefix: "sampah*"
Pengumuman::whereFullText(['judul', 'isi'], $kata.'*', ['mode' => 'boolean'])->paginate();

Full-text InnoDB secara default mengabaikan kata yang lebih pendek dari innodb_ft_min_token_size (3 karakter). Untuk kebutuhan pencarian yang lebih kaya (typo, relevansi, faset), pertimbangkan Laravel Scout dengan Meilisearch atau Typesense.

Langkah 8: Ganti OFFSET dengan Keyset Pagination

paginate() menghasilkan LIMIT 50 OFFSET 500000 di halaman ke-10.001. MySQL tetap harus membaca dan membuang 500.000 baris pertama, sehingga halaman makin lambat makin ke belakang. Ditambah lagi query COUNT(*) untuk total halaman.

// Keyset (cursor) pagination: WHERE (jatuh_tempo, id) > (nilai terakhir) LIMIT 50
$tunggakan = Iuran::where('rukun_tetangga_id', $rt)
    ->where('status', 'belum_bayar')
    ->orderBy('jatuh_tempo')
    ->orderBy('id')
    ->cursorPaginate(50);

Agar efisien, index harus mendukung kolom urutan: (rukun_tetangga_id, status, jatuh_tempo, id). Di InnoDB, primary key id sudah ikut tersimpan di setiap secondary index, sehingga index (rukun_tetangga_id, status, jatuh_tempo) umumnya sudah cukup.

Konsekuensi cursor pagination: tidak ada nomor halaman, hanya "berikutnya" dan "sebelumnya". Untuk daftar admin yang butuh nomor halaman, simplePaginate() (tanpa COUNT) adalah jalan tengah.

Langkah 9: Menambah Index di Tabel Besar Production

Di MySQL 8, menambah secondary index umumnya dilakukan online (ALGORITHM=INPLACE, LOCK=NONE): tabel tetap bisa dibaca dan ditulis selama index dibangun. Meski begitu:

  • pembangunan index memakan I/O dan bisa memperlambat server; jalankan di luar jam sibuk,
  • di awal dan akhir proses tetap ada metadata lock singkat yang bisa tertahan oleh transaksi panjang yang sedang berjalan, dan selama tertahan, query lain ke tabel itu ikut antre,
  • pada replikasi, replika menjalankan perubahan yang sama dan bisa tertinggal.

Paksa MySQL menolak bila operasi tidak bisa online, alih-alih diam-diam mengunci tabel:

public function up(): void
{
    DB::statement('ALTER TABLE iuran ADD INDEX iuran_rt_status_tempo_idx (rukun_tetangga_id, status, jatuh_tempo), ALGORITHM=INPLACE, LOCK=NONE');
}

Atur batas tunggu metadata lock agar migrasi gagal cepat alih-alih menahan seluruh aplikasi:

DB::statement('SET SESSION lock_wait_timeout = 10');

Untuk tabel yang sangat besar atau perubahan yang tidak bisa online (mengubah tipe kolom, primary key), gunakan alat seperti gh-ost atau pt-online-schema-change.

Langkah 10: Index yang Tidak Terpakai dan Terlalu Banyak

Setiap index mempercepat baca tetapi memperlambat INSERT/UPDATE/DELETE dan memakan memori buffer pool. Temukan yang tidak terpakai:

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'rw';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'rw';

Statistik ini dihitung sejak MySQL terakhir di-restart; pastikan server sudah berjalan cukup lama (meliputi akhir bulan dan proses laporan) sebelum mengambil keputusan.

Sebelum menghapus, jadikan index invisible dulu (MySQL 8). Optimizer mengabaikannya, tetapi index tetap dipelihara sehingga bisa dikembalikan seketika bila ternyata dibutuhkan:

ALTER TABLE iuran ALTER INDEX iuran_rukun_tetangga_id_foreign INVISIBLE;
-- pantau beberapa hari; bila aman:
-- ALTER TABLE iuran DROP INDEX ...;   (atau kembalikan: ... VISIBLE)

Langkah 11: Statistik dan Query Optimizer

Kadang index sudah ada tetapi MySQL memilih index yang salah karena statistik tidak akurat:

ANALYZE TABLE iuran;

Bila setelah itu pilihan masih buruk, periksa lagi rancangan index sebelum memaksa dengan hint. Hint FORCE INDEX bisa dipakai lewat query builder, tetapi menjadi beban pemeliharaan karena tidak menyesuaikan saat data berubah:

Iuran::query()->from(DB::raw('iuran FORCE INDEX (iuran_rt_status_tempo_idx)'))->...;

Langkah 12: Test bahwa Query Tetap Memakai Index

Index bisa "hilang efeknya" ketika seseorang mengubah query (menambah whereYear, mengganti urutan kolom). Test berikut berjalan di CI yang memakai MySQL (bukan SQLite):

// tests/Feature/Database/IndexTest.php
it('query tunggakan memakai composite index', function () {
    $rencana = Iuran::query()
        ->where('rukun_tetangga_id', 1)
        ->where('status', 'belum_bayar')
        ->where('jatuh_tempo', '<', '2026-10-19')
        ->orderBy('jatuh_tempo')
        ->explain();

    expect($rencana->first()->key)->toBe('iuran_rt_status_tempo_idx')
        ->and($rencana->first()->type)->not->toBe('ALL');
})->skip(fn () => DB::getDriverName() !== 'mysql', 'butuh MySQL');

Pada tabel yang hampir kosong, optimizer bisa memilih full scan karena memang lebih murah. Isi beberapa ribu baris lewat factory di test ini, atau jalankan test terhadap database staging yang berisi data realistis.

Troubleshooting

Error / Gejala Penyebab Solusi
type: ALL padahal index ada Fungsi pada kolom, konversi tipe, atau urutan composite salah Langkah 5 dan 6
Index ada tetapi key NULL di data kecil Optimizer memilih full scan karena lebih murah Uji dengan volume realistis
Using filesort pada halaman daftar Kolom ORDER BY tidak ada di akhir index Tambahkan kolom urutan ke composite index
Using temporary pada laporan GROUP BY/DISTINCT tanpa index pendukung Index sesuai kolom grup; atau pra-agregasi
Halaman terakhir pagination sangat lambat OFFSET besar cursorPaginate()
Migrasi index menggantung dan aplikasi ikut macet Metadata lock tertahan transaksi panjang lock_wait_timeout, jalankan di luar jam sibuk
Insert makin lambat Terlalu banyak index Hapus index tidak terpakai (invisible dulu)
Specified key was too long Index pada VARCHAR panjang utf8mb4 Batasi panjang kolom atau index prefix
Hasil whereFullText kosong untuk kata pendek innodb_ft_min_token_size Sesuaikan konfigurasi dan bangun ulang index
Optimizer memilih index yang salah Statistik usang ANALYZE TABLE

Best Practices

Keamanan

  • Jangan menampilkan output toRawSql() atau EXPLAIN di halaman yang bisa diakses pengguna.
  • Slow query log bisa memuat nilai data; batasi akses ke file log dan atur rotasinya.
  • Gunakan pengguna database dengan hak terbatas untuk aplikasi; ALTER hanya untuk pengguna migrasi bila memungkinkan.

Performa

  • Index mengikuti pola query nyata: kesamaan dulu, lalu rentang/urutan.
  • Hindari fungsi pada kolom yang difilter; tulis kondisi rentang.
  • Keyset pagination untuk tabel besar; simplePaginate bila nomor halaman tidak penting.
  • Agregasi di database, dan covering index untuk laporan yang sering dibuka.

Maintainability

  • Beri nama index secara eksplisit agar mudah dirujuk di test dan migrasi.
  • Setiap index baru disertai alasan (query mana yang didukung) di pesan commit atau komentar migrasi.
  • Tinjau index tidak terpakai secara berkala; hapus lewat tahap invisible.
  • Test EXPLAIN untuk query paling kritis di CI dengan MySQL.

Related Articles

Last Tested

Komponen Versi
Laravel 13.x
PHP 8.4
MySQL 8.4 LTS
Percona Toolkit 3.x
Pest 4.x
Tanggal 19 Oktober 2026

Bagikan artikel:

Artikel lainnya