Laravel: Atasi Sort by Latest yang Lambat di Tabel Transaksi Besar

Pada aplikasi Laravel, gejala paling umum di halaman daftar transaksi adalah query terasa cepat saat data masih sedikit, lalu mendadak melambat ketika tabel membesar. Pola yang sering muncul adalah query dengan ORDER BY created_at DESC untuk menampilkan data terbaru, ditambah filter seperti status, tenant_id, atau rentang tanggal. Dalam kasus seperti ini, bottleneck utamanya biasanya bukan Laravel, melainkan database yang terpaksa melakukan scan besar atau filesort.

Solusi yang paling sering efektif adalah memilih index komposit yang mengikuti pola WHERE + ORDER BY, bukan hanya menambahkan index tunggal pada setiap kolom. Jika query Anda memfilter tenant_id dan status, lalu mengurutkan berdasarkan created_at DESC, maka index terpisah pada masing-masing kolom sering tidak cukup. Database butuh jalur index yang sesuai dengan pola akses data agar bisa memfilter sekaligus mengembalikan hasil dalam urutan yang benar tanpa sort tambahan.

Studi kasus: gejala query latest makin lambat

Misalkan Anda punya tabel transactions yang terus bertambah. Halaman admin atau dashboard tenant menampilkan transaksi terbaru dengan filter opsional.

// Contoh Query Builder Laravel
$query = DB::table('transactions')
    ->where('tenant_id', $tenantId)
    ->where('status', $status)
    ->orderByDesc('created_at')
    ->paginate(50);

Atau dengan filter rentang tanggal:

$query = Transaction::query()
    ->where('tenant_id', $tenantId)
    ->whereBetween('created_at', [$startDate, $endDate])
    ->latest('created_at')
    ->paginate(50);

Sekilas query tersebut terlihat sederhana. Namun saat jumlah data sudah besar, database mungkin harus:

• membaca terlalu banyak baris karena filter tidak cocok dengan index yang tersedia,
• mengumpulkan kandidat hasil terlebih dahulu,
• lalu melakukan sort terpisah untuk memenuhi ORDER BY created_at DESC.

Di sinilah Anda mulai melihat gejala seperti:

• halaman list transaksi lambat hanya pada tenant tertentu,
• response time melonjak saat filter status diaktifkan,
• pagination halaman awal masih lumayan, tetapi halaman lebih dalam makin berat,
• EXPLAIN menunjukkan Using filesort atau jumlah baris yang dibaca terlalu besar.

Kenapa ORDER BY created_at DESC bisa lambat

1. Index tunggal pada created_at tidak selalu cukup

Banyak developer mulai dengan menambahkan index pada created_at:

$table->index('created_at');

Index ini membantu jika query Anda memang hanya mengambil data terbaru secara global. Tetapi saat query juga memfilter tenant_id atau status, database sering tidak bisa memanfaatkan index tersebut secara optimal. Alasannya sederhana: urutan data pada index created_at tidak dikelompokkan berdasarkan tenant atau status.

Akibatnya, database mungkin harus menelusuri banyak entri terbaru dari seluruh tabel, lalu menyaring satu per satu sampai menemukan baris yang cocok. Pada tabel besar, ini mahal.

2. Index tunggal pada kolom filter juga belum tentu menyelesaikan sorting

Misalnya Anda menambah index pada tenant_id dan status:

$table->index('tenant_id');
$table->index('status');

Ini bisa membantu proses penyaringan, tetapi belum tentu membantu pengurutan created_at DESC. Database mungkin tetap perlu mengekstrak baris yang lolos filter lalu mengurutkannya di luar index. Itulah yang sering tampil sebagai filesort.

Catatan: Istilah filesort tidak selalu berarti sort ke disk. Ini merujuk pada proses sort tambahan di luar traversal index. Walaupun bisa terjadi di memori, tetap saja itu biaya ekstra yang ingin Anda hindari pada query panas.

3. Kombinasi WHERE + ORDER BY menentukan index yang efektif

Untuk query seperti ini:

SELECT *
FROM transactions
WHERE tenant_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 50;

Index yang sering paling relevan adalah:

(tenant_id, status, created_at)

Mengapa? Karena database bisa:

1. masuk ke segmen index untuk tenant_id tertentu,
2. mempersempit lagi ke status tertentu,
3. lalu membaca data langsung dalam urutan created_at tanpa sort tambahan.

Ini jauh lebih efisien daripada mengandalkan beberapa index tunggal yang masing-masing hanya membantu sebagian masalah.

Memilih index komposit yang benar

Prinsip umum

Untuk kasus daftar transaksi terbaru, urutan kolom index sebaiknya mengikuti pola berikut:

1. kolom equality filter terlebih dahulu,
2. kolom untuk sort setelahnya,
3. kolom range perlu dipertimbangkan hati-hati karena bisa membatasi kemampuan index untuk sorting.

Artinya, untuk query yang paling sering dijalankan:

WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC

index yang masuk akal adalah:

(tenant_id, status, created_at)

Contoh pola index berdasarkan query nyata

Pola 1: filter tenant + status, lalu latest

WHERE tenant_id = ? AND status = ?
ORDER BY created_at DESC

Rekomendasi awal:

(tenant_id, status, created_at)

Pola 2: filter tenant saja, lalu latest

WHERE tenant_id = ?
ORDER BY created_at DESC

Rekomendasi awal:

(tenant_id, created_at)

Pola 3: filter tenant + rentang tanggal

WHERE tenant_id = ?
  AND created_at BETWEEN ? AND ?
ORDER BY created_at DESC

Rekomendasi awal:

(tenant_id, created_at)

Karena filter equality pada tenant_id diikuti akses berdasarkan created_at, index ini sering tetap efektif.

Pola 4: filter tenant + status + rentang tanggal, lalu latest

WHERE tenant_id = ?
  AND status = ?
  AND created_at BETWEEN ? AND ?
ORDER BY created_at DESC

Rekomendasi awal:

(tenant_id, status, created_at)

Dalam pola ini, created_at tetap berada di belakang equality filter. Database dapat menavigasi segmen tenant dan status, lalu memproses rentang sekaligus urutan waktu.

Kapan satu index tidak cukup

Masalah nyata biasanya muncul karena aplikasi tidak punya satu pola query saja. Misalnya:

• halaman A sering memakai tenant_id + status + latest,
• halaman B sering memakai tenant_id + latest,
• export memakai tenant_id + date range.

Dalam kondisi seperti ini, Anda mungkin butuh lebih dari satu index komposit. Namun jangan menambah index tanpa alasan. Setiap index baru menambah biaya INSERT, UPDATE, dan ukuran penyimpanan.

Pilih berdasarkan query paling sering, paling lambat, dan paling penting untuk UX.

Implementasi di Laravel: migration dan query

Menambahkan index komposit lewat migration

Misalnya Anda sering menjalankan query tenant + status + latest.

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('transactions', function (Blueprint $table) {
            $table->index(['tenant_id', 'status', 'created_at'], 'trx_tenant_status_created_idx');
        });
    }

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

Jika pola query lain juga dominan, Anda bisa menambah index berbeda, misalnya:

$table->index(['tenant_id', 'created_at'], 'trx_tenant_created_idx');

Jangan langsung hapus index lama sebelum Anda memastikan tidak ada query lain yang masih bergantung padanya.

Contoh Eloquent yang sesuai dengan index

$transactions = Transaction::query()
    ->where('tenant_id', $tenantId)
    ->where('status', $status)
    ->orderByDesc('created_at')
    ->paginate(50);

Jika Anda menggunakan latest(), hasilnya setara selama kolom yang dipakai memang created_at:

$transactions = Transaction::query()
    ->where('tenant_id', $tenantId)
    ->where('status', $status)
    ->latest('created_at')
    ->paginate(50);

Pastikan query yang dikirim ke database benar-benar konsisten dengan index yang Anda buat. Perubahan kecil pada filter bisa membuat optimizer memilih rencana yang berbeda.

Hindari SELECT berlebihan bila tidak perlu

Kalau halaman hanya menampilkan beberapa kolom, jangan selalu mengambil semuanya:

$transactions = Transaction::query()
    ->select(['id', 'tenant_id', 'status', 'amount', 'created_at'])
    ->where('tenant_id', $tenantId)
    ->where('status', $status)
    ->latest('created_at')
    ->paginate(50);

Ini tidak selalu mengubah strategi index, tetapi tetap mengurangi beban I/O dan transfer data, terutama jika tabel punya banyak kolom besar.

Cara membaca EXPLAIN secara dasar

Sebelum menambah index, lihat dulu bagaimana database mengeksekusi query. Anda tidak perlu menghafal semua kolom EXPLAIN. Fokus pada beberapa hal yang paling relevan:

• key: index apa yang dipilih,
• rows: perkiraan berapa banyak baris yang dibaca,
• Extra: apakah ada Using filesort, Using where, atau indikasi lain.

Contoh query SQL hasil dari Eloquent

EXPLAIN
SELECT id, tenant_id, status, amount, created_at
FROM transactions
WHERE tenant_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Apa yang perlu dicurigai

Kasus buruk:

• key kosong atau memakai index yang tidak relevan,
• rows sangat besar dibanding hasil yang diambil,
• Extra menunjukkan Using filesort.

Kasus yang lebih sehat:

• database memilih index komposit yang Anda targetkan,
• jumlah rows lebih kecil atau lebih terfokus,
• indikasi sort tambahan berkurang atau hilang.

Perlu diingat, EXPLAIN adalah estimasi dan bisa berbeda dari eksekusi nyata. Namun untuk kasus optimasi sort by latest pada tabel transaksi besar, EXPLAIN biasanya cukup membantu untuk melihat apakah index Anda sejalan dengan query.

Tips praktis saat hasil EXPLAIN tidak sesuai harapan

• Pastikan urutan kolom pada index sudah benar.
• Cek apakah query nyata sama dengan asumsi Anda, termasuk filter opsional.
• Lihat apakah ada kondisi OR, fungsi pada kolom, atau cast yang membuat index sulit dipakai.
• Pastikan statistik database cukup mutakhir jika sistem Anda membutuhkannya.
• Uji dengan data produksi atau snapshot yang representatif, bukan hanya data lokal yang kecil.

Dampak pada pagination: offset vs cursor

Kenapa offset pagination bisa tetap mahal

Meskipun index sudah lebih baik, paginate() berbasis offset tetap punya batas. Pada halaman yang dalam, database harus melewati banyak baris sebelum mengambil data yang diminta.

$transactions = Transaction::query()
    ->where('tenant_id', $tenantId)
    ->where('status', $status)
    ->latest('created_at')
    ->paginate(50);

Untuk halaman awal, ini biasanya masih cukup baik. Tetapi jika user sering membuka halaman sangat jauh, biaya offset bisa terasa.

Kapan mempertimbangkan cursor pagination

Jika kebutuhan utama adalah menelusuri transaksi terbaru secara berurutan, cursorPaginate() sering lebih stabil daripada offset pagination.

$transactions = Transaction::query()
    ->where('tenant_id', $tenantId)
    ->where('status', $status)
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->cursorPaginate(50);

Menambahkan urutan sekunder seperti id berguna untuk menjaga urutan tetap deterministik ketika ada banyak baris dengan created_at yang sama.

Namun ada trade-off:

• cursor pagination tidak cocok untuk kebutuhan lompat bebas ke halaman ke-100,
• implementasi UI biasanya perlu menyesuaikan pola next/previous,
• urutan query harus stabil dan konsisten.

Jika kasus Anda memang sekadar daftar transaksi terbaru, cursor pagination sering layak dipertimbangkan setelah index dibereskan.

Trade-off: index cepat untuk read, tapi ada biaya write

Setiap index tambahan memberi keuntungan pada query baca tertentu, tetapi juga menambah biaya saat:

• INSERT transaksi baru,
• UPDATE kolom yang masuk index seperti status,
• operasi pemeliharaan storage dan cache.

Pada sistem transaksi dengan volume tulis tinggi, terlalu banyak index bisa menjadi masalah baru. Karena itu:

• hindari membuat banyak index yang saling tumpang tindih tanpa data pendukung,
• utamakan query paling kritis,
• evaluasi apakah index lama masih dipakai.

Contoh tumpang tindih yang perlu ditinjau:

• sudah ada (tenant_id, status, created_at),
• lalu Anda juga menambah (tenant_id, status) tanpa alasan kuat,
• atau terlalu banyak index tunggal yang tidak lagi diperlukan.

Keputusan akhir harus berbasis pola query riil, bukan asumsi umum.

Kesalahan umum yang sering membuat optimasi gagal

1. Menambah index pada semua kolom secara terpisah

Ini kesalahan paling sering. Index tunggal pada tenant_id, status, dan created_at belum tentu menggantikan manfaat index komposit yang sesuai dengan query.

2. Urutan kolom index salah

Index (created_at, tenant_id, status) tidak sama efeknya dengan (tenant_id, status, created_at). Untuk query yang memfilter tenant dan status lalu mengurutkan latest, urutan sangat penting.

3. Menganggap semua query latest butuh index yang sama

Query tenant + latest dan tenant + status + latest bisa butuh strategi berbeda. Jangan memaksa satu index untuk semua pola jika hasil EXPLAIN menunjukkan sebaliknya.

4. Tidak menguji dengan data yang realistis

Query yang terlihat cepat di lokal dengan ribuan baris belum tentu sehat di produksi dengan jutaan baris. Uji pada data yang representatif.

5. Fokus pada Laravel, padahal bottleneck ada di SQL

Mengganti Query Builder ke Eloquent atau sebaliknya biasanya tidak menyelesaikan akar masalah jika rencana eksekusi SQL tetap buruk.

Checklist verifikasi sebelum dan sesudah deploy

Sebelum deploy

1. Identifikasi query daftar transaksi yang paling sering dipakai dan paling lambat.
2. Ambil SQL nyata dari aplikasi, bukan versi yang disederhanakan.
3. Jalankan EXPLAIN pada query tersebut.
4. Catat index yang saat ini dipilih, jumlah rows, dan apakah ada Using filesort.
5. Tentukan 1-2 index komposit yang benar-benar relevan dengan pola WHERE + ORDER BY.
6. Periksa apakah index baru akan tumpang tindih dengan index lama.
7. Uji migration pada environment staging atau salinan data yang cukup besar.

Sesudah deploy

1. Jalankan EXPLAIN lagi pada query yang sama.
2. Pastikan database mulai memilih index komposit yang diharapkan.
3. Bandingkan latensi query sebelum dan sesudah deploy dari log atau monitoring yang Anda miliki.
4. Perhatikan dampak pada insert/update transaksi, terutama jika volume write tinggi.
5. Amati halaman pagination yang paling sering diakses, bukan hanya halaman pertama.
6. Evaluasi kembali index lama yang mungkin sudah tidak diperlukan.

Rekomendasi praktis untuk kasus ini

Jika Anda menghadapi sort by latest yang lambat di tabel transaksi besar Laravel, langkah yang paling aman dan langsung bisa diterapkan adalah:

1. petakan query riil yang memakai ORDER BY created_at DESC,
2. cek filter dominan seperti tenant_id, status, dan rentang tanggal,
3. buat index komposit yang mengikuti pola WHERE + ORDER BY, misalnya (tenant_id, status, created_at),
4. verifikasi dengan EXPLAIN apakah filesort atau scan besar berkurang,
5. pertimbangkan cursorPaginate() jika offset pagination tetap berat.

Intinya, masalah ini jarang selesai hanya dengan menambah index pada created_at. Pada tabel transaksi yang terus membesar, performa ditentukan oleh apakah database bisa memfilter dan mengurutkan lewat jalur index yang sama. Begitu strategi index Anda selaras dengan pola query, halaman latest biasanya membaik secara signifikan tanpa perlu mengubah arsitektur aplikasi.