Index with Sort #

Dalam praktik sehari-hari, banyak query database yang terlihat sederhana tapi diam-diam mahal biayanya. Salah satu penyebab paling umum adalah proses sorting (ORDER BY). Sorting sering dianggap “gratis”, padahal di balik layar database bisa melakukan operasi yang sangat berat: full scan, in-memory sort, atau bahkan disk-based sort.

Di sinilah index dengan aspek sort (ordered index) menjadi sangat krusial. Artikel ini membahas:

• Apa itu sorting di index
• Kapan index bisa (dan tidak bisa) membantu ORDER BY
• Bagaimana database memanfaatkan index untuk sorting
• Best practice desain index terkait sorting

Artikel ini fokus pada konsep dan best practice, bukan spesifik vendor, meskipun contoh logikanya relevan untuk MySQL, PostgreSQL, dan database relasional modern.

Apa Itu Sorting di Index? #

Secara fundamental, index database selalu tersimpan dalam keadaan terurut.

Contoh sederhana:

CREATE INDEX idx_users_created_at ON users(created_at);

Index di atas secara fisik tersimpan dalam struktur seperti B-Tree, di mana created_at sudah sorted ascending.

Artinya:

• Database tidak perlu melakukan sort ulang
• Engine cukup melakukan index scan dari awal ke akhir (atau sebaliknya)

Inilah yang disebut sebagai index-assisted sorting.

Masalah Besar: Sorting Tanpa Index #

Pertimbangkan query berikut:

SELECT * FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

Jika tidak ada index yang mendukung created_at:

1. Database membaca semua row dengan status = 'PAID'
2. Menyimpan hasilnya di memory (atau disk jika besar)
3. Melakukan sorting
4. Baru mengambil 20 data teratas

Konsekuensi:

• CPU tinggi
• Memory pressure
• Disk temporary table
• Latency tidak linear (data sedikit cepat, data besar mendadak lambat)

⚠️ LIMIT tidak menyelamatkan query tanpa index sort

Kapan Sorting di Index Dibutuhkan? #

Query dengan ORDER BY yang Sering Dipakai #

Jika sebuah endpoint selalu memanggil data terurut, maka index sort hampir selalu dibutuhkan.

Contoh umum:

• Timeline (ORDER BY created_at DESC)
• Log aktivitas
• Riwayat transaksi
• Data admin panel (latest first)

Query dengan LIMIT Kecil Tapi Data Besar #

Contoh klasik:

SELECT * FROM logs
ORDER BY created_at DESC
LIMIT 10;

Tanpa index:

• Tetap scan seluruh table

Dengan index:

• Database cukup membaca 10 entry pertama dari index

Perbedaan kompleksitas:

• Tanpa index: O(n log n)
• Dengan index: O(log n + k)

Sorting Setelah Filtering (Composite Index) #

Sorting hampir selalu datang bersamaan dengan WHERE.

Contoh:

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Index ideal:

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Kenapa?

• Database bisa filter user_id dulu
• Data hasil filter sudah terurut
• Tidak ada sort tambahan

Composite Index dan Sorting #

Urutan Kolom Sangat Penting #

Index berikut:

(user_id, created_at)

✔️ Optimal untuk:

WHERE user_id = ? ORDER BY created_at

❌ Tidak optimal untuk:

WHERE created_at > ? ORDER BY user_id

Rule penting: sorting hanya efektif jika kolom ORDER BY berada setelah kolom equality (=) di index

Arah Sorting (ASC / DESC) #

Sebagian besar database modern:

• Bisa scan index maju atau mundur
• Tidak selalu butuh index terpisah untuk ASC dan DESC

Namun:

• Composite index dengan mixed order tetap harus hati-hati

Contoh:

(user_id ASC, created_at DESC)

Gunakan hanya jika:

• Query sangat konsisten
• Arah sorting benar-benar fixed

Covering Index dan Sorting #

Index menjadi jauh lebih powerful jika:

• Semua kolom SELECT ada di index

Contoh:

SELECT id, created_at
FROM orders
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 10;

Index:

(user_id, created_at, id)

Manfaat:

• Tidak perlu lookup ke table
• Zero random I/O
• Latency sangat rendah

Ini sering disebut:

• Covering index
• Index-only scan

Kapan Index Sorting Tidak Berguna? #

ORDER BY dengan Function #

ORDER BY DATE(created_at)

❌ Index tidak bisa dipakai

Solusi:

• Simpan kolom ter-derivasi
• Atau gunakan generated column

ORDER BY Kolom yang Tidak Selektif #

Contoh:

ORDER BY status

Jika status hanya punya 3 nilai:

• Index jarang membantu
• Sorting cost tetap tinggi

ORDER BY Random #

ORDER BY RAND()

❌ Tidak bisa dioptimasi dengan index

Best Practice Desain Index untuk Sorting #

Selalu Analisis Query Nyata #

Bukan teori.

• Lihat query paling sering dipanggil
• Perhatikan ORDER BY + LIMIT

Gabungkan Filter + Sort dalam Satu Index #

WHERE A = ? AND B = ? ORDER BY C

Index:

(A, B, C)

Jangan Membuat Index Hanya Karena ORDER BY #

Index punya biaya:

• Write lebih lambat
• Storage bertambah

Buat index hanya jika:

• Query dipanggil sering
• Latency penting

Gunakan EXPLAIN, Bukan Perasaan #

Pastikan:

• Using index
• Tidak ada Using filesort

Ringkasan Mental Model #

Penutup #

Sorting bukan sekadar urusan tampilan data, tapi salah satu faktor performa paling mahal di database.

Index yang dirancang dengan benar bisa mengubah:

• Query 5–8 detik
• Menjadi < 50 ms

Namun index yang salah justru:

• Membebani write
• Memboroskan storage
• Tidak pernah dipakai

Desain index untuk sorting bukan soal teori, tapi membaca pola akses data.