CodeIgniter 4 memungkinkan pengamatan langsung terhadap query, namun tabel yang terus tumbuh bisa membuat query yang tadinya cepat jadi lambat. Artikel ini langsung menjawab kebutuhan backend engineer: langkah audit query lambat, pemahaman EXPLAIN, penyesuaian indeks berbasis pola filter dan sort, pagination cursor/seek, serta monitoring pertumbuhan data agar indeks tetap relevan tanpa over-indexing.

Observasi Query Lambat di CodeIgniter 4

Langkah pertama adalah menemukan query yang menjadi bottleneck. Aktifkan profiler atau gunakan log query untuk mengetahui durasi. Di environment yang lebih matang, Anda bisa menggabungkan toolbar Debugger dengan $db->getLastQuery() atau menggunakan query logger bawaan.

$db = \.Config\Database::connect();
$builder = $db->table('transaksi')
    ->where('status', 'selesai')
    ->orderBy('created_at', 'DESC')
    ->limit(50);

$query = $builder->get();
debug($db->getLastQuery());

Selain itu, aktifkan query logging di file app/Config/Database.php hanya di lingkungan pengujian:

public $default = [
    'DBDriver' => 'MySQLi',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug' => true,
    'charset' => 'utf8mb4',
    'DBCollat' => 'utf8mb4_general_ci',
    'swapPre' => '',
    'encrypt' => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port' => 3306,
    'enableQueryLogging' => true,
];

Catat query yang paling sering muncul atau paling lambat. Tools seperti slow query log database (MySQL/MariaDB) atau EXPLAIN plan dari CLI juga menambah konteks.

Memahami EXPLAIN untuk Menemukan Bottleneck

Setelah mengidentifikasi query, jalankan EXPLAIN untuk melihat alasan lambatnya query. Fokus pada kolom seperti type (gunakan ALL berarti full table scan) dan Extra (menunjukkan filesort atau temporary).

EXPLAIN SELECT *
FROM transaksi
WHERE status = 'selesai'
ORDER BY created_at DESC
LIMIT 50;

Jika database menunjukkan filesort atau Using where; Using temporary, artinya query tidak menggunakan indeks secara efisien. Catat urutan kolom di WHERE dan ORDER BY untuk dijadikan dasar desain indeks.

Perhatikan juga selektivitas kondisi: filter dengan nilai banyak (misalnya status LIKE '%x%') tidak akan membantu indeks. Pertimbangkan menambahkan kolom tambahan untuk menghindari LIKE wildcard yang membatasi indeks.

Index Adaptif Berdasarkan Pola Filter dan Sort

Setelah memahami pola query, buat atau ubah indeks agar sesuai dengan kombinasi WHERE dan ORDER BY. Jika query sering memanggil status dan created_at, indeks komposit (status, created_at) akan membantu.

CREATE INDEX idx_transaksi_status_created ON transaksi (status, created_at);

Indeks komposit berguna ketika kolom pertama sesuai dengan kondisi filter. Jika query tambahan membutuhkan kolom user_id, pertimbangkan indeks lain atau indeks komposit lebih besar? Jangan menumpuk indeks tanpa analisis: setiap indeks menambah beban pada INSERT/UPDATE/DELETE.

Untuk beberapa database (PostgreSQL atau MySQL/MariaDB 10.2+), indeks parsial/partial dapat diterapkan agar hanya baris tertentu yang disimpan. Misalnya jika kebanyakan query hanya membaca data status = 'selesai', maka indeks parsial dapat menyaring kondisi tersebut (di MySQL/MariaDB dapat mensimulasikan dengan generated column yang menyaring status lalu menambahkan indeks pada kolom itu).

Sementara itu, indeks cover bisa menjadikan query berjalan tanpa mengakses tabel. Jika query hanya membaca status, created_at, dan jumlah, buat indeks yang menyertakan ketiga kolom tersebut sehingga database cukup membaca indeks (misalnya dengan INCLUDE di PostgreSQL atau MySQL 8 dengan INVISIBLE jika perlu).

Catatan trade-off: Setiap indeks menambah waktu tulis dan membutuhkan ruang. Lakukan index regiment secara berkala: tabel besar perlu dievaluasi ulang setiap kali pola query berubah.

Pagination Efisien dengan Cursor / Seek

Pagination tradisional LIMIT offset tidak skalabel di tabel besar. Gunakan pendekatan seek pagination dengan menyimpan kursor terakhir (mis. id atau created_at). Contoh implementasi di CodeIgniter 4:

$lastCreatedAt = $this->request->getGet('last_created_at');
$builder = $db->table('transaksi')
    ->where('status', 'selesai')
    ->orderBy('created_at', 'DESC')
    ->limit(20);

if ($lastCreatedAt) {
    $builder->where('created_at <', $lastCreatedAt);
}

$result = $builder->get()->getResult();

Gunakan index yang sama dengan yang dipakai query utama agar pagination juga memanfaatkan indeks. Untuk menghindari inkonsistensi saat data berubah, tambahkan stabilisasi seperti ORDER BY created_at DESC, id DESC dan filter tambahan id jika created_at sama.

Monitoring Pertumbuhan Data dan Validasi Indeks

Monitoring proaktif penting agar indeks tetap relevan. Skenario tumbuhnya data bisa memengaruhi selektivitas indeks. Terapkan script cron atau task CLI yang mencatat:

  • Volume baris per tabel (misalnya dengan SELECT COUNT(*) atau statistik information_schema).
  • Rasio penggunaan indeks (menggunakan SHOW INDEX FROM tabel atau views statistik database).
  • Frekuensi query lambat dari slow log.

Atur threshold untuk memicu review indeks saat tabel tumbuh 30%-50% dari checkpoint terakhir. Untuk CI4, integrasikan task CLI dengan sentry log atau dashboard monitoring seperti Grafana.

Jaga agar tidak terjadi over-indexing: indeks yang jarang dipakai lebih baik dihapus karena memakan ruang dan menurunkan performa tulis. Gunakan buffer penghapusan dengan memeriksa index_usage (jika database mendukung) atau memantau EXPLAIN historis.

Kesimpulan

Audit query lambat di CodeIgniter 4 harus dimulai dari observasi log hingga penggunaan EXPLAIN. Setelah bottleneck diketahui, desain indeks adaptif sesuai filter dan sort nyata, lalu padukan dengan pagination cursor serta monitoring pertumbuhan data. Perlakukan indeks sebagai bagian hidup dari siklus aplikasi: evaluasi, tambahkan, dan hapus sesuai kebutuhan untuk menjaga performa di tabel bertumbuh.