Pertumbuhan tabel, filter bertingkat, dan permintaan data yang heterogen membuat query Laravel bisa melambat drastis. Kunci penanganannya adalah memetakan bottleneck dari slow query log, memeriksa rencana eksekusi dengan EXPLAIN, lalu memilih index partial yang menargetkan kondisi WHERE/ORDER utama.

Dokumen ini menunjukkan langkah-langkah praktis agar Anda bisa menerapkan optimasi tersebut secara tepat, memahami dampak terhadap pagination, dan tahu kapan harus pindah ke strategi skala besar seperti read replica atau denormalisasi.

Mengidentifikasi Query yang Membutuhkan Optimasi

Membaca slow query log

Aktifkan slow query log (MySQL/MariaDB: set slow_query_log=ON, PostgreSQL: log_min_duration_statement) lalu ambil entri yang berulang dan melebihi ambang waktu. Fokus pada query-filter yang dipanggil user interface filter bertingkat. Catat struktur WHERE/ORDER-nya agar dapat dibandingkan dengan index saat ini.

Analisis EXPLAIN

Jalankan EXPLAIN (atau EXPLAIN ANALYZE di PostgreSQL) untuk setiap query. Perhatikan kolom:

  • type/rows: Apakah query melakukan full table scan?
  • possible_keys/key: Index apa yang tersedia dan dipakai?
  • Extra: Adakah Using filesort atau Using temporary?

Jika rencana menunjukkan Using where tanpa index atau scan semuanya, berarti index yang ada tidak memenuhi filter bertingkat Anda.

Menilai Kolom Filter Bertingkat untuk Index Partial

Tarik daftar kolom yang sering digunakan di WHERE, JOIN, ORDER BY, lalu urutkan berdasarkan frekuensi eksekusi. Filter bertingkat umumnya menggunakan kombinasi kolom dengan kondisi pasif tertentu (misal: status = 'active'). Di sinilah partial index berperan: hanya mengindeks subset baris yang paling sering dicari.

Contoh kondisi yang layak: status tetap, tenant_id yang relatif kecil, atau flag boolean. Jangan indeks semua kolom sekaligus karena akan membuat index berat dan memperlambat DML.

Gunakan SHOW INDEX FROM orders (MySQL) atau pg_indexes untuk memastikan index sebelumnya tidak redundan.

Menerapkan Partial Index dari Laravel

Laravel tidak menyediakan helper khusus untuk partial index, tapi Anda bisa memanfaatkan DB::statement di migration. Contoh Postgres:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class AddPartialIndexToOrders extends Migration
{
    public function up()
    {
        DB::statement(<<<'SQL'
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_active ON orders (user_id, created_at)
WHERE status = 'active' AND deleted_at IS NULL;
SQL
        );
    }

    public function down()
    {
        DB::statement("DROP INDEX IF EXISTS idx_orders_active;");
    }
}

Untuk MySQL, Anda bisa memanfaatkan generated column lalu mengindeksnya atau menggunakan index prefix. Contoh: membuat kolom virtual is_active yang hanya bernilai 1 saat filter terpenuhi, lalu membuat index di atasnya. Pastikan migration dijalankan di lingkungan staging sebelum produksi.

Kenapa pendekatan ini bekerja? Karena partial index hanya memelihara stack pointer ke baris relevan filter, sehingga skip full scan tanpa membebani write path dengan index super besar.

Dampak Index terhadap Pagination dan Cursor

Filter bertingkat sering disertai pagination. Jika menggunakan offset-based pagination, query tetap harus melewati baris hingga posisi offset, meskipun index sudah ada. Partial index membantu mengurangi jumlah baris yang dibaca, tapi offset masih mahal karena tantangan skip. Gunakan cursor-based pagination (contoh: WHERE (created_at, id) < (?, ?)) agar database memanfaatkan index secara langsung.

Kombinasikan index partial dengan ORDER BY created_at DESC, id DESC yang sesuai struktur index Anda agar pagination tetap konsisten. Jika offset terpaksa digunakan, batasi nilai offset dan pertimbangkan cache halaman populer untuk mengurangi frekuensi akses.

Kapan Beralih ke Denormalisasi atau Read Replica

Jika partial index sudah optimal tapi tabel terus tumbuh dan query tetap heavy karena join kompleks atau agregasi besar, pertimbangkan:

  • Denormalisasi: Simpan key summary di tabel lain atau materialized view khusus filter tertentu. Ini menambah kompleksitas update data tapi menghindari join berat.
  • Read replica: Tarik traffic read-heavy ke replica; tetap fokus index di replica agar read tidak bersaing dengan write.

Kedua strategi punya biaya pemeliharaan: denormalisasi perlu job sinkronisasi, sementara replica menambah latensi replikasi dan memerlukan monitoring lag.

Kesimpulan

Optimasi query Laravel dengan filter bertingkat dan tabel besar harus dimulai dari diagnosis yang akurat: baca slow query log, pahami EXPLAIN, kemudian desain partial index yang menargetkan bagian dataset paling sering dipakai. Tunjukkan juga efeknya pada pagination, lalu siap untuk fallback ke denormalisasi atau replica jika beban tidak mereda. Pendekatan berlapis seperti ini menjaga performa tanpa mengorbankan konsistensi.