Gejala: Latensi Tinggi dan Disk Spill pada Data Table Inertia

Saat membangun fitur data table dinamis menggunakan stack Inertia.js (Laravel + Vue/React), performa sering kali terdegradasi seiring pertumbuhan volume baris data. Masalah ini tampak nyata ketika pengguna mengaktifkan filter status sekaligus mengurutkan kolom tertentu.

Permintaan partial reload via router.get() dengan header X-Inertia: true mengalami lonjakan Time to First Byte (TTFB) hingga ratusan milidetik atau beberapa detik. Profiling log query database menunjukkan indikasi bottleneck berikut:

-- Log MySQL / MariaDB EXPLAIN
Extra: Using index condition; Using filesort

-- Log Performance Schema
Sort_merge_passes > 0
Sort_rows > 0

Label Using filesort menandakan bahwa mesin database (misalnya InnoDB) tidak dapat memanfaatkan struktur pohon B-Tree untuk mengekstrak data dalam urutan yang diminta. Database terpaksa menyalin baris yang lolos filter ke buffer memori (sort_buffer_size). Jika volume data melebihi alokasi memori tersebut, database melakukan spill ke temporary table di disk (sort merge passes), menyebabkan latensi I/O yang masif.

Root Cause: Pelanggaran Aturan Equality-Sort-Range (ESR)

Akar penyebab filesort adalah mismatch antara klausa query Eloquent dan urutan kolom pada indeks komposit (composite index). B-Tree menyimpan data secara terurut berdasarkan kombinasi kolom indeks dari kiri ke kanan (leftmost prefix).

Penyusunan indeks komposit wajib mengikuti kaidah Equality-Sort-Range (ESR):

  1. Equality: Kolom yang dicocokkan dengan operator nilai pasti (=, IS NULL) harus diletakkan pada posisi paling depan indeks.
  2. Sort: Kolom yang digunakan dalam klausa ORDER BY diletakkan tepat setelah kolom equality. Hal ini memungkinkan database membaca leaf nodes yang sudah terurut secara fisik tanpa proses sort tambahan.
  3. Range: Kolom dengan filter rentang nilai (>, <, BETWEEN, LIKE 'prefix%') harus diletakkan di posisi paling akhir karena operasi range membatalkan pembacaan sekuensial terurut untuk kolom indeks berikutnya.

Pertimbangkan query umum pada controller data table berikut:

SELECT id, tenant_id, status, created_at, amount 
FROM orders 
WHERE tenant_id = 1 
  AND status = 'paid'
ORDER BY created_at DESC 
LIMIT 25 OFFSET 0;

Jika tabel hanya memiliki indeks terpisah per kolom seperti INDEX (tenant_id) dan INDEX (created_at), atau composite index yang salah seperti INDEX (created_at, tenant_id, status), query planner akan memilih:

  • Menggunakan indeks created_at untuk menghindari sort, lalu memindai baris satu per satu untuk memeriksa filter tenant_id dan status (Full Index Scan berbiaya tinggi).
  • Menggunakan indeks tenant_id untuk memfilter data, lalu melempar hasil ke memori untuk disortir manual berdasarkan created_at (Filesort).

Implementasi Solusi: Migrasi Index dan Optimalisasi Controller

1. Rancang Composite Index Sesuai Aturan ESR

Terapkan composite index baru melalui migrasi database Laravel dengan mendahulukan kolom filter identitas dan status, diikuti oleh kolom sort.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            // Drop indeks lama yang redundan jika ada
            $table->dropIndex(['created_at']);
            
            // Indeks komposit mengikuti kaidah ESR: (Equality, Equality, Sort)
            $table->index(['tenant_id', 'status', 'created_at'], 'idx_orders_tenant_status_created');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex('idx_orders_tenant_status_created');
        });
    }
};

2. Sesuaikan Query Controller dan Hilangkan Redundant Sorting

Kesalahan umum di backend Laravel adalah menambahkan tie-breaker sorting seperti ->orderBy('id', 'desc') di akhir query secara statis. Menambahkan kolom yang tidak tercakup dalam composite index akan langsung merusak urutan B-Tree dan memicu filesort kembali.

namespace App\Http\Controllers;

use App\Models\Order;
use Illuminate\Http\Request;
use Inertia\Inertia;
use Inertia\Response;

class OrderController extends Controller
{
    public function index(Request $request): Response
    {
        $validated = $request->validate([
            'status' => ['nullable', 'string', 'in:paid,pending,failed'],
            'direction' => ['nullable', 'in:asc,desc'],
            'page' => ['nullable', 'integer'],
        ]);

        $tenantId = $request->user()->tenant_id;
        $status = $validated['status'] ?? 'paid';
        $direction = $validated['direction'] ?? 'desc';

        // Query mematuhi struktur B-Tree indeks komposit
        $orders = Order::query()
            ->select(['id', 'tenant_id', 'status', 'created_at', 'amount', 'reference_number'])
            ->where('tenant_id', $tenantId)
            ->where('status', $status)
            ->orderBy('created_at', $direction)
            // HINDARI: ->orderBy('id', 'desc') karena merusak pemanfaatan urutan indeks komposit
            ->paginate(25)
            ->withQueryString();

        return Inertia::render('Orders/Index', [
            'orders' => $orders,
            'filters' => [
                'status' => $status,
                'direction' => $direction,
            ],
        ]);
    }
}
Catatan: Jika pagination deterministik membutuhkan tie-breaker unik, kolom id harus dimasukkan ke dalam indeks komposit pada posisi paling akhir: ['tenant_id', 'status', 'created_at', 'id'], dan query harus memuat orderBy('created_at', $dir)->orderBy('id', $dir).

Validasi: Evaluasi EXPLAIN ANALYZE Sebelum vs Sesudah

Uji eksekusi query pada dataset berisi 1.500.000 baris menggunakan perintah native EXPLAIN ANALYZE MySQL 8.0.

Sebelum Optimasi (Hanya single index per kolom)

-> Limit: 25 row(s)  (cost=14285.20 rows=25) (actual time=248.512..248.518 rows=25 loops=1)
    -> Sort: orders.created_at DESC, limit input to 25 row(s) per chunk  (cost=14285.20 rows=152400) (actual time=248.508..248.514 rows=25 loops=1)
        -> Filter: ((orders.tenant_id = 1) and (orders.status = 'paid'))  (cost=1524.30 rows=152400) (actual time=0.082..180.412 rows=150000 loops=1)
            -> Index lookup on orders using idx_tenant_id (tenant_id=1)  (cost=1524.30 rows=300000) (actual time=0.075..112.301 rows=300000 loops=1)

Hasil: Database membaca 300.000 baris, memfilter 150.000 baris, lalu mengeksekusi operasi Sort di memori/disk yang memakan waktu 248.5 ms.

Sesudah Optimasi (Composite Index ESR)

-> Limit: 25 row(s)  (cost=3.21 rows=25) (actual time=0.041..0.065 rows=25 loops=1)
    -> Index lookup on orders using idx_orders_tenant_status_created (tenant_id=1, status='paid') (reverse)  (cost=3.21 rows=25) (actual time=0.039..0.061 rows=25 loops=1)

Hasil: Operasi Sort hilang sepenuhnya dari execution plan. Database melakukan backward scan langsung pada leaf nodes indeks dan berhenti tepat setelah 25 baris ditemukan. Waktu eksekusi turun menjadi 0.065 ms.

Dampak pada Inertia Response TTFB

  • Sebelum optimasi: TTFB request /orders?status=paid berkisar antara 280ms - 350ms pada beban normal, melonjak saat concurrency naik.
  • Sesudah optimasi: TTFB request Inertia turun ke kisaran 25ms - 40ms (sebagian besar durasi dialokasikan untuk bootstrap framework dan JSON serialization props).