Cara Mengoptimalkan Query MySQL Laravel Menggunakan Index dan EXPLAIN
Admin HijrahKreativa
Penulis
Source code aplikasi, template web, dan ebook. Unduh dari akun setelah bayar. Lihat produk
Admin HijrahKreativa
Penulis
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.
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:
EXPLAIN dan EXPLAIN ANALYZE,| 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']);
});
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;
$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 |
+----+-------+------+---------------------------+---------------------------+--------+----------+-----------------------------+
| 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.
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.
Aturan urutan kolom dalam composite index:
=, IN) lebih dulu.<, >, BETWEEN, LIKE 'abc%') atau kolom pengurutan (ORDER BY).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']);
| 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');
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.
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.
Di MySQL 8, menambah secondary index umumnya dilakukan online (ALGORITHM=INPLACE, LOCK=NONE): tabel tetap bisa dibaca dan ditulis selama index dibangun. Meski begitu:
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.
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)
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)'))->...;
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.
| 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 |
Keamanan
toRawSql() atau EXPLAIN di halaman yang bisa diakses pengguna.ALTER hanya untuk pengguna migrasi bila memungkinkan.Performa
simplePaginate bila nomor halaman tidak penting.Maintainability
EXPLAIN untuk query paling kritis di CI dengan MySQL.| Komponen | Versi |
|---|---|
| Laravel | 13.x |
| PHP | 8.4 |
| MySQL | 8.4 LTS |
| Percona Toolkit | 3.x |
| Pest | 4.x |
| Tanggal | 19 Oktober 2026 |
Sebelumnya
Redis untuk Laravel: Panduan Setup Cache, Session, dan Queue Worker
Berikutnya
Laravel Cache: Strategi Cache Data, Query, dan Halaman Tanpa Menyebabkan Data Kedaluwarsa
Dokumen ini menjelaskan prosedur konfigurasi autentikasi SSH Key antara server Ubuntu dan GitHub untuk memungkinkan server melakukan operasi Git, seperti clone, pull, dan fetch tanpa menggunakan username dan password akun GitHub.
Peta jalan arsitektur dari satu server hingga banyak server: tahapan pertumbuhan dan sinyal kapan naik tahap, prinsip yang sebaiknya diterapkan sejak awal (stateless, asinkron, data ber-tenant), read replica, pengelolaan data yang terus tumbuh, degradasi yang anggun saat layanan bermasalah, feature...
Menyiapkan lingkungan uji yang jujur, mengukur satu endpoint dengan ApacheBench, mensimulasikan perilaku pengguna sungguhan dengan k6 (skenario, threshold, autentikasi token), menelusuri penyebab request lambat dengan Telescope, memantau server selama pengujian, serta menulis laporan hasil dan angga...
Bagaimana Octane membuat Laravel lebih cepat dengan mem-boot aplikasi sekali dan menyimpannya di memori, apa yang sebenarnya dipercepat (dan yang tidak), risiko kebocoran state antar-request yang wajib dipahami, cara memeriksa kode agar aman untuk Octane, pemasangan dengan FrankenPHP di production,...
03 Nov 2026
19 Oct 2026
19 Oct 2026
19 Oct 2026
19 Oct 2026
19 Oct 2026
19 Oct 2026
19 Oct 2026
19 Oct 2026
19 Oct 2026
HijrahKreativa
Kami balas secepatnya
Masuk dulu untuk chat dengan tim kami. Riwayat chat Anda tersimpan di akun.
Masuk