Ketika tabel transaksi di aplikasi CodeIgniter 4 terus tumbuh, query yang semula cepat bisa menjadi tersendat karena pemindaian penuh (full table scan). Solusi langsungnya adalah menemukan query biang lambat, memberi index yang memanfaatkan pola SELECT/WHERE/GROUP BY, dan menyesuaikan pagination agar tidak mengandalkan offset besar. Artikel ini menyajikan langkah-langkah teknis untuk melakukannya tanpa mengorbankan konsistensi.

Langkah pertama yang harus selalu dilakukan adalah menjalankan EXPLAIN pada query bermasalah agar Anda tahu kolom mana yang jadi bottleneck. Setelah analisis, tentukan kolom yang masuk akal untuk digabungkan dalam index komposit, lalu rancang pagination yang sesuai beban—offset hanya untuk halaman awal, seek/cursor atau chunked untuk scroll panjang.

1. Identifikasi Query Lambat dengan EXPLAIN dan Profiling

Jalankan EXPLAIN pada query dengan WHERE, JOIN, GROUP BY, atau ORDER BY. Fokus pada kolom yang menyebabkan rows besar atau penggunaan type seperti ALL. Contoh sederhana:

EXPLAIN SELECT * FROM transactions
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

Perhatikan key_len, possible_keys, dan apakah query membaca seluruh tabel. Jika tidak ada index yang digunakan, tambahkan index (atau susunan index komposit) berdasarkan urutan filtering dan sorting.

Untuk monitoring berkelanjutan, aktifkan profiling matriks sederhana di CodeIgniter 4:

// app/Config/Logger.php
public $handlers = [
    'CodeIgniter\Log\Handlers\FileHandler' => [
        'handles' => ['critical', 'error', 'warning', 'debug', 'info']
    ]
];

// Dalam Controller, nyalakan debug saat pengujian
$this->db->enableQueryBuilder(true);
$this->db->enableCache(false);

Gunakan fitur profiling bawaan untuk mencatat query lambat, atau periksa slow query log MySQL/PostgreSQL untuk query > 1 detik.

2. Merancang Index Komposit yang Optimal

Index komposit harus mengikuti urutan kolom yang digunakan dalam WHERE, JOIN, lalu ORDER BY. Ambil contoh query:

SELECT id, amount FROM transactions
WHERE customer_id = ?
AND status = 'completed'
ORDER BY created_at DESC
LIMIT 20;

Index optimal: (customer_id, status, created_at) karena mencerminkan filtering dan sorting. Implementasi minimal:

ALTER TABLE transactions
ADD INDEX idx_trans_customer_status_created_at
(customer_id, status, created_at DESC);

Index DESC mendukung ORDER BY created_at DESC tanpa sorting tambahan. Perhatikan trade-off berikut:

  • Ukuran index bertambah seiring kolom ditambahkan; hindari kolom teks panjang.
  • Biaya update/insert naik karena index harus diperbarui; pastikan frekuensi write tetap wajar.
  • Jangan menambahkan index untuk setiap kombinasi; utamakan query yang sering muncul di reporting atau API publik.

Kapan menambahkan index baru tanpa downtime? Strategi praktis:

  • Bangun index di replika baca terlebih dahulu, lalu lakukan ALTER TABLE ... di primary saat maintenance singkat.
  • Gunakan fitur ONLINE DDL (MySQL) jika tersedia untuk menghindari penguncian panjang.
  • Di lingkungan terbatas, lakukan index build di tabel copy, lalu swap nama tabel setelah sinkron selesai.

3. Model dan Query Builder untuk Query Terkontrol

Contoh Model CodeIgniter 4 dengan query paginated:

namespace App\Models;

use CodeIgniter\Model;

class TransactionModel extends Model
{
    protected $table = 'transactions';
    protected $allowedFields = ['customer_id', 'amount', 'status', 'created_at'];

    public function findByCustomer(int $customerId, int $limit = 20)
    {
        return $this->builder()
            ->where('customer_id', $customerId)
            ->where('status', 'completed')
            ->orderBy('created_at', 'DESC')
            ->limit($limit)
            ->get()
            ->getResultArray();
    }
}

Builder di atas langsung memanfaatkan index komposit yang sudah dibangun. Untuk query yang memerlukan count atau agregasi, pastikan kolom yang digunakan sudah tercakup di index.

4. Strategi Pagination: Offset vs Seek vs Cursor

Offset pagination mudah diimplementasikan, tetapi performanya menurun ketika OFFSET besar karena database tetap meng-scan N baris untuk mencapai posisi tersebut. Gunakan hanya untuk halaman awal.

Seek pagination memanfaatkan kolom terurut (seperti created_at) dan hanya mengambil data setelah nilai terakhir yang sudah ditampilkan:

SELECT * FROM transactions
WHERE customer_id = ?
AND created_at < ?
ORDER BY created_at DESC
LIMIT 20;

Seek lebih efisien karena menggunakan index untuk menentukan posisi tanpa skip N baris.

Cursor-based pagination lebih tepat untuk API stateless. Kirim token (misalnya kombinasi created_at + id) dan gunakan sebagai parameter WHERE. Jika data baru ditambahkan, cursor memastikan tidak ada duplikasi.

Untuk hasil besar sekaligus, pertimbangkan chunked reading dengan chunk() pada Model (CodeIgniter 4) atau streaming output. Gunakan chunk size 500–1000 untuk menjaga memori:

$model->chunk(1000, function($rows) {
    foreach ($rows as $row) {
        // proses batch
    }
});

5. Monitoring, Logging, dan Pengujian Index Tanpa Downtime

Aktifkan slow query log pada database dengan threshold yang realistis (misal > 1 detik), dan ambil sample query dari log tersebut untuk dianalisis. Gunakan Tools seperti Percona Toolkit (misalnya pt-query-digest) untuk mengelompokkan query lambat.

Untuk menguji dampak index tanpa downtime:

  • Bangun index di replika baca/instance staging dengan data representatif.
  • Gunakan EXPLAIN ANALYZE untuk membandingkan biaya query sebelum dan sesudah index.
  • Jika database mendukung, buat index dengan ALGORITHM=INPLACE atau ONLINE agar tidak mengunci tabel.
  • Sesuaikan maintenance window jika perubahan diharuskan di primary; pastikan aplikasi menangani error sementara.

6. Menggabungkan Pagination dengan Caching

Pagination dengan offset kecil cocok untuk caching page pertama. Strategi yang umum dipakai:

  • Cache hasil halaman pertama, lalu hapus cache saat data transaksi baru masuk (misalnya event-driven).
  • Untuk pagination seek/cursor, cache token terakhir per pengguna jika aplikasi memungkinkan.
  • Gunakan cache berbasis key-value (Redis/Memcached) untuk query lookup (misalnya total balance per customer) agar tidak memaksa query berat berulang.

Namun, jangan cache terlalu lama jika data sangat sering berubah. Selalu kombinasikan dengan mekanisme invalidasi atau TTL pendek.

Kesimpulan

Query lambat di tabel transaksi bertumbuh bisa ditangani dengan pendekatan bertahap: identifikasi menggunakan EXPLAIN, rancang index komposit berdasarkan pola query, pilih pagination yang sesuai beban, dan monitor perubahan performa. Uji index baru di lingkungan aman sebelum rollout, serta pertimbangkan caching selektif agar pagination tetap responsif tanpa menambah beban database. Pendekatan ini menjaga CI4 tetap scalable seiring volume data meningkat.