Saat data masih kecil, query list dengan filter, sorting, dan pagination di CodeIgniter 4 biasanya terasa normal. Masalah baru terlihat ketika jumlah baris membesar: halaman daftar mulai lambat, CPU database naik, dan query yang memakai WHERE + ORDER BY + LIMIT tetap mahal walau kolom filter atau kolom sort sudah diberi index tunggal.

Dalam banyak kasus, bottleneck-nya bukan sekadar “belum ada index”, tetapi pola index tidak cocok dengan bentuk query. Di sinilah index komposit dan covering index sering membantu. Artikel ini fokus pada cara mempercepat query list di CodeIgniter 4 secara praktis, tanpa klaim berlebihan dan tanpa bergantung pada perilaku spesifik satu database tertentu.

Gejala Nyata Saat Query List Mulai Menjadi Bottleneck

Masalah ini biasanya muncul pada halaman admin, dashboard operasional, daftar transaksi, katalog produk, atau riwayat aktivitas. Ciri-cirinya antara lain:

  • Query dengan LIMIT 20 tetap lambat karena database harus menyaring dan mengurutkan banyak baris terlebih dahulu.

  • Index sudah ada di kolom status atau created_at, tetapi performa tidak banyak berubah.

  • Eksekusi cepat pada data uji, lalu melambat drastis saat data produksi tumbuh.

  • EXPLAIN menunjukkan database masih membaca banyak baris atau masih melakukan operasi sort tambahan.

Contoh pola query yang sering bermasalah:

SELECT id, order_no, customer_name, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Secara intuitif, developer sering mencoba dua index tunggal:

INDEX(status)
INDEX(created_at)

Sayangnya, dua index tunggal ini belum tentu cukup. Database mungkin harus memilih salah satu index yang paling masuk akal, lalu tetap melakukan pekerjaan tambahan untuk sort atau lookup ke tabel utama. Hasilnya, query masih mahal.

Mengapa Index Tunggal Sering Gagal Membantu

Masalahnya ada pada kombinasi operasi

Query list umumnya tidak hanya memfilter, tetapi juga mengurutkan dan membatasi hasil. Jika index hanya cocok untuk filter atau hanya cocok untuk sort, database bisa tetap melakukan kerja ekstra.

Pada query berikut:

SELECT id, order_no, customer_name, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Database idealnya ingin:

  1. Menemukan baris dengan status = 'paid'.

  2. Mengambilnya dalam urutan created_at DESC.

  3. Berhenti cepat setelah 20 baris.

Jika hanya ada INDEX(status), database bisa cepat menemukan baris berstatus paid, tetapi belum tentu langsung dalam urutan created_at. Jika hanya ada INDEX(created_at), database bisa membaca sesuai urutan waktu, tetapi harus mengecek banyak baris sampai menemukan 20 yang statusnya paid.

Sort dan lookup bisa tetap mahal

Masalah lain adalah table lookup atau akses balik ke data utama. Walau index dipakai untuk menemukan kandidat baris, database mungkin masih perlu membaca tabel utama untuk mengambil kolom yang diminta di SELECT. Jika terjadi berulang pada banyak baris, biaya I/O tetap besar.

Di sinilah konsep covering index menjadi relevan.

Memahami Index Komposit dan Covering Index

Index komposit

Index komposit adalah index yang terdiri dari beberapa kolom dalam urutan tertentu. Untuk query sebelumnya, pola yang sering lebih cocok adalah:

INDEX(status, created_at)

Dengan pola ini, database berpeluang:

  • memakai status untuk filter awal,

  • memakai created_at untuk menjaga urutan hasil,

  • lebih cepat berhenti setelah memenuhi LIMIT.

Urutan kolom sangat penting. INDEX(created_at, status) tidak selalu setara dengan INDEX(status, created_at). Untuk query yang memfilter status lalu mengurutkan created_at, biasanya urutan filter yang paling selektif dan paling konsisten dipakai harus dipertimbangkan dengan hati-hati.

Covering index

Covering index berarti semua kolom yang dibutuhkan query sudah tersedia di index, sehingga database bisa mengurangi atau menghindari akses tambahan ke tabel utama. Contoh:

SELECT id, order_no, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Jika tersedia index komposit yang juga mencakup kolom-kolom hasil select yang diperlukan, query bisa menjadi lebih efisien. Bentuk fisik dan perilakunya berbeda antar database, tetapi prinsip umumnya sama: semakin sedikit lookup tambahan ke data utama, semakin ringan query baca.

Catatan: covering index bukan berarti “masukkan semua kolom ke index”. Tujuannya adalah mencakup kolom yang benar-benar diperlukan oleh query yang sering dan penting. Index yang terlalu lebar justru menambah ukuran storage dan biaya tulis.

Pola Index yang Tepat untuk WHERE, ORDER BY, dan LIMIT

Contoh kasus yang realistis

Misalkan tabel orders dipakai untuk daftar transaksi terbaru berdasarkan status:

SELECT id, order_no, status, total_amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Pola index yang sering layak dievaluasi:

INDEX(status, created_at)

Jika query list ini benar-benar sering dipakai dan kolom hasilnya sedikit serta stabil, Anda bisa mempertimbangkan index yang juga membantu aspek covering. Implementasi detailnya bergantung pada database engine, tetapi prinsip auditnya sama: cek apakah query masih membutuhkan banyak lookup ke tabel utama atau tidak.

Kapan menambahkan kolom lain ke index

Tambahkan kolom ke index hanya jika ada alasan jelas, misalnya:

  • query yang sama sangat sering dipanggil,

  • kolom pada SELECT sedikit dan stabil,

  • EXPLAIN atau profil query menunjukkan biaya lookup masih tinggi.

Contoh pola yang lebih lebar:

INDEX(status, created_at, id, order_no)

Namun ini bukan aturan umum. Menambahkan terlalu banyak kolom bisa membuat index besar, memperlambat INSERT/UPDATE, dan belum tentu dipakai sesuai harapan. Mulailah dari index komposit yang sempit dan paling relevan.

Aturan praktis memilih urutan kolom

  • Mulai dari kolom yang dipakai konsisten pada WHERE.

  • Lanjutkan dengan kolom untuk ORDER BY jika pola query memang tetap.

  • Pertimbangkan kolom hasil SELECT hanya jika kebutuhan covering jelas.

  • Hindari membuat banyak index mirip yang tumpang tindih tanpa audit.

Untuk query dengan beberapa filter, misalnya:

SELECT id, order_no, status, created_at
FROM orders
WHERE tenant_id = 10 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Maka kandidat pola bisa berubah menjadi:

INDEX(tenant_id, status, created_at)

Urutan ini sering masuk akal jika data dipisahkan kuat per tenant dan hampir semua query selalu menyertakan tenant_id.

Implementasi Query di CodeIgniter 4

Contoh Query Builder yang umum

<?php

$orders = $this->db->table('orders')
    ->select('id, order_no, status, total_amount, created_at')
    ->where('status', 'paid')
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->get()
    ->getResultArray();

Dari sisi CodeIgniter 4, Query Builder membantu menyusun SQL yang rapi, tetapi performa utamanya tetap ditentukan oleh desain query dan index di database. Framework tidak bisa “mengakali” index yang tidak cocok.

Membatasi kolom select

Salah satu langkah sederhana namun sering berdampak adalah jangan pakai select('*') untuk halaman list. Ambil hanya kolom yang memang ditampilkan atau dipakai:

<?php

$orders = $this->db->table('orders')
    ->select('id, order_no, status, created_at')
    ->where('status', 'paid')
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->get()
    ->getResultArray();

Semakin sedikit kolom yang diminta, semakin besar peluang index membantu lebih efektif, termasuk untuk skenario covering.

Melihat SQL hasil Query Builder

Jika perlu audit, lihat SQL final yang dikirim:

<?php

$query = $this->db->table('orders')
    ->select('id, order_no, status, created_at')
    ->where('status', 'paid')
    ->orderBy('created_at', 'DESC')
    ->limit(20);

$sql = $query->getCompiledSelect();

Ini berguna untuk memastikan tidak ada kondisi tambahan, join, atau urutan sort yang membuat index sulit dipakai.

Cara Membaca EXPLAIN Secara Umum

Perintah EXPLAIN berbeda detailnya antar database, tetapi tujuannya sama: melihat bagaimana database berencana mengeksekusi query. Fokuslah pada pertanyaan berikut, bukan hanya pada nama kolom output yang bisa berbeda-beda:

  • Apakah query memakai index yang diharapkan?

  • Berapa banyak baris yang diperkirakan harus dibaca?

  • Apakah masih ada operasi sort terpisah?

  • Apakah masih ada lookup tambahan ke tabel utama yang besar?

Contoh penggunaan

EXPLAIN
SELECT id, order_no, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Apa yang perlu dicari

  1. Index yang dipakai
    Jika database tetap memilih full scan atau index yang tidak sesuai, pola index Anda mungkin belum cocok atau statistik database belum membantu optimizer membuat keputusan baik.

  2. Jumlah baris yang dibaca
    Untuk query list dengan LIMIT kecil, idealnya jumlah baris yang dibaca tidak terlalu besar. Jika EXPLAIN menunjukkan pembacaan banyak baris, index belum efektif menekan ruang pencarian.

  3. Operasi sort tambahan
    Jika database masih perlu sort terpisah setelah filter, index kemungkinan belum mendukung urutan ORDER BY dengan baik.

  4. Akses ke tabel utama
    Jika lookup ke tabel utama masih dominan, pertimbangkan apakah query perlu semua kolom itu atau apakah skenario covering layak dipakai.

Tip debugging: bandingkan EXPLAIN sebelum dan sesudah menambah index. Jangan hanya melihat bahwa “index dipakai”, tetapi lihat juga apakah jumlah baris yang dibaca dan kebutuhan sort benar-benar turun.

Contoh Audit Sebelum Menambah Index

1. Catat query paling mahal

Jangan mengoptimasi berdasarkan dugaan. Ambil query nyata dari log aplikasi, slow query log, profiler, atau monitoring APM yang tersedia.

2. Pastikan bentuk query stabil

Jika halaman list punya banyak variasi filter dan sort dinamis, satu index tidak akan cocok untuk semua kombinasi. Optimasi sebaiknya fokus pada pola paling sering dan paling penting.

3. Periksa select yang berlebihan

Sering kali query lambat bukan hanya karena sort, tetapi karena mengambil banyak kolom yang tidak dipakai UI.

4. Audit index yang sudah ada

Jangan buru-buru menambah index baru jika sudah ada index mirip. Index yang tumpang tindih membebani write tanpa manfaat sebanding.

5. Uji dengan data yang representatif

Data kecil sering menipu. Gunakan volume data yang mendekati produksi agar perilaku optimizer dan biaya I/O lebih realistis.

Trade-off dan Batasan Covering Index

Biaya tulis naik

Setiap index tambahan harus dipelihara saat INSERT, UPDATE, dan DELETE. Jika tabel sangat aktif ditulis, index terlalu banyak bisa menurunkan throughput write.

Ukuran index membesar

Covering index yang terlalu lebar memakan storage lebih besar dan bisa mengurangi efisiensi cache. Ini sebabnya tidak semua query list layak dibuatkan covering index penuh.

Tidak cocok untuk semua variasi query

Jika pengguna bisa sort berdasarkan banyak kolom berbeda, Anda tidak mungkin membuat index ideal untuk semua kombinasi tanpa ledakan jumlah index. Pilih pola yang paling sering dipakai dan paling penting terhadap performa bisnis.

Optimizer bisa memilih rencana lain

Walau index sudah dibuat, database belum tentu selalu memakainya. Distribusi data, statistik, dan bentuk query bisa membuat optimizer memilih jalur berbeda. Karena itu, validasi dengan EXPLAIN tetap wajib.

Kesalahan Umum yang Sering Terjadi

  • Mengandalkan INDEX(status) dan INDEX(created_at) lalu berharap query kombinasi filter-sort otomatis cepat.

  • Menambah index pada semua kolom yang terlihat penting tanpa melihat query nyata.

  • Memakai select('*') untuk halaman list.

  • Tidak memperhatikan urutan kolom pada index komposit.

  • Mengukur performa hanya dari local environment dengan data kecil.

  • Menganggap covering index selalu solusi terbaik untuk semua kasus.

Checklist Audit Sebelum dan Sesudah Optimasi

Sebelum

  • Apakah query lambat yang diukur benar-benar berasal dari halaman list?

  • Apakah pola WHERE + ORDER BY + LIMIT konsisten?

  • Apakah hanya kolom yang dibutuhkan yang diambil?

  • Apakah sudah ada index komposit yang relevan?

  • Apakah volume data uji mendekati kondisi nyata?

  • Apakah EXPLAIN menunjukkan scan besar atau sort tambahan?

Sesudah

  • Apakah EXPLAIN menunjukkan index yang lebih sesuai?

  • Apakah estimasi atau jumlah baris yang dibaca turun?

  • Apakah kebutuhan sort tambahan berkurang?

  • Apakah waktu respons query membaik pada data representatif?

  • Apakah dampak ke INSERT/UPDATE masih bisa diterima?

  • Apakah ada index lama yang kini redundan dan bisa dievaluasi ulang?

Penutup

Untuk mempercepat query list di CodeIgniter 4, masalah utamanya sering bukan sekadar “tambahkan index”, melainkan samakan pola index dengan bentuk query. Pada kombinasi WHERE, ORDER BY, dan LIMIT, index tunggal sering tidak cukup. Index komposit membantu database memfilter dan mengurutkan lebih efisien, sedangkan covering index dapat mengurangi lookup tambahan jika dipakai secara terukur.

Mulailah dari query yang paling sering dan paling mahal, batasi kolom SELECT, buat kandidat index yang sesuai urutan akses, lalu validasi dengan EXPLAIN. Dengan pendekatan ini, optimasi menjadi terarah dan lebih aman dibanding menambah index secara acak.