Memperbaiki Query Lambat Tanpa Menunggu Tabel Meledak

Masalah utama tim backend adalah pertumbuhan tabel transaksi yang menyebabkan query historis menjadi lambat. Di CodeIgniter 4, solusi yang efektif dimulai dari memilah langkah-langkah diagnosis dan optimasi: pertama cari tahu mengapa query lambat, lalu perbaiki indeks dan pola pagination, serta kontrol dampak growth dengan monitoring dan caching. Artikel ini membawa Anda langsung ke judul utama tanpa teori berlebihan.

Analisis yang dipandu fakta (EXPLAIN, profiler, log) memperlihatkan area bottleneck nyata, bukan asumsi. Pendekatan ini menjaga konsistensi: tidak ada perubahan arsitektur polesan ringan, melainkan perbaikan terukur agar tim tetap bisa mengirim transaksi tanpa gangguan.

Diagnosa Query: EXPLAIN dan Profiler

Langkah pertama adalah mengeksekusi EXPLAIN pada query yang terasa berat. Perhatikan apakah query memilih full table scan, menggunakan indeks yang sesuai, serta perkiraan row count yang melonjak.

Misalnya:

EXPLAIN SELECT t.id, t.tanggal, u.nama FROM transaksi t JOIN users u ON t.user_id = u.id WHERE t.status = 'selesai' ORDER BY t.tanggal DESC LIMIT 50;

Jika type masih ALL dan rows jutaan, artinya indeks tidak digunakan. Lanjutkan dengan profiling di CodeIgniter:

Aktifkan profiler (tidak dianjurkan di lingkungan produksi) dengan:

$this->output->setProfiler(true);

Profiler akan menunjukkan query yang paling banyak memakan waktu/rate. Gabungkan data ini dengan log slow query database (MySQL slow_query_log) untuk menangkap query yang tidak selalu melewati layer aplikasi.

Strategi Indexing untuk Kolom Filter dan Join

Setiap filter dan join harus berada pada kolom yang memiliki indeks berguna. Untuk tabel transaksi yang besar, indeks yang sederhana sering kali tidak cukup. Pertimbangkan indeks komposit berdasarkan pola query:

  • Filter status + tanggal: CREATE INDEX idx_transaksi_status_tanggal ON transaksi(status, tanggal DESC);
  • Join user_id: CREATE INDEX idx_transaksi_user ON transaksi(user_id);

Gunakan EXPLAIN ulang setelah menambahkan indeks untuk memastikan query memilih idx_transaksi_status_tanggal.

Dalam contoh nyata, bottleneck terjadi karena kolom status berada pada akhir indeks, sementara query mengurutkan berdasarkan tanggal. Penambahan indeks komposit yang menempatkan status di depan dan tanggal urutan menurun mempercepat pencarian periode tertentu.

Jangan lupa bahwa terlalu banyak indeks memperlambat operasi tulis. Pertimbangkan trade-off: tambahkan indeks hanya untuk query paling sering dijalankan atau gunakan indeks parsial (jika tersedia) untuk status tertentu.

Pagination Efisien Menggunakan Cursor/Seek

Pembacaan halaman dengan OFFSET semakin lambat saat tabel tumbuh. Gunakan teknik cursor (atau seek pagination) dengan mempergunakan kolom yang berurutan, misalnya id atau tanggal.

Contoh implementasi CodeIgniter 4:

$cursor = $this->request->getGet('cursor') ?? 0;
$builder = $this->db->table('transaksi');
$builder->select('id, tanggal, total');
$builder->where('status', 'selesai');
$builder->where('id >', (int) $cursor);
$builder->orderBy('id', 'ASC');
$builder->limit(50);
$results = $builder->get()->getResult();

Setelah menampilkan hasil, kirim cursor terbaru (misalnya ID terakhir) ke frontend untuk permintaan berikutnya. Cara ini menghindari overhead OFFSET, karena database hanya membaca baris baru setelah cursor.

Trade-off: pagination cursor tidak cocok untuk audit akses acak (perlu kembali ke halaman tertentu). Dalam kasus tersebut, kombinasikan cursor dengan bookmark yang disimpan di sisi klien.

Mengukur Dampak Pertumbuhan Data

Ukuran tabel bertumbuh berpengaruh langsung pada query. Gunakan metric sederhana untuk memantau:

  • Trigger background job yang mengecek jumlah baris per hari dan mencatatnya di log khusus.
  • Gunakan SHOW TABLE STATUS LIKE 'transaksi'; untuk melihat Rows dan Data_length.
  • Integrasikan dengan sistem monitoring (Prometheus + exporter SQL) untuk alert jika response time query utama melewati threshold.

Dokumenkan pertumbuhan data bersama timeline release fitur baru. Jika pertumbuhan tiba-tiba, cari tahu apakah ada batch insert tanpa indeks atau job lama yang tidak di-archives.

Perbaiki schema sederhana: misalnya memindahkan data detail transaksi (line item) ke tabel terpisah, sehingga tabel transaksi utama tetap ramping. Hanya simpan referensi ID dan total: ini membuat indeks tetap kecil dan menjaga join tetap cepat.

Cache Result dan Monitoring Slow Query

Untuk query yang dijalankan berkali-kali dan tidak memerlukan data paling real-time, manfaatkan caching CodeIgniter:

$cache = \Config\Services::cache();
$key = 'transaksi:summary:'.$status;
$cached = $cache->get($key);
if ($cached) {
    return $cached;
}
// jalankan query dan simpan
$result = $builder->get()->getResult();
$cache->save($key, $result, 60);

Pilih TTL singkat agar cache tidak mengorbankan konsistensi. Gunakan invalidasi manual ketika ada event penting (misalnya selesai checkout).

Monitoring slow query bisa dilakukan melalui:

  • Enable slow query log di database dengan threshold (misalnya > 2 detik).
  • Gunakan CodeIgniter logging untuk mencatat query dengan waktu eksekusi tinggi (manfaatkan Query::getDuration() saat profiler aktif).
  • Alert otomatis dengan tool seperti Grafana ketika rata-rata waktu query utama naik.

Dengan memadukan cache ringan dan monitoring, Anda menjaga performa tanpa risiko konsistensi tinggi. Jika ada anomaly, profiling dan EXPLAIN kembali wajib dijalankan.