Mengapa Paginasi SQL Menggunakan OFFSET Bisa Menjadi Lambat ?

Paginasi adalah kebutuhan umum dalam aplikasi web yang perlu menampilkan dataset besar secara efisien. Banyak pengembang menggunakan query SQL dengan klausa ORDER BY dan LIMIT, sering kali menggabungkannya dengan offset seperti LIMIT 10 OFFSET 20. Namun, seiring pertumbuhan dataset, metode ini dapat menyebabkan masalah performa.

Artikel ini akan mengeksplorasi masalah performa dari penggunaan ORDER BY dalam paginasi dan memperkenalkan solusi yang lebih efisien dengan menggunakan id < current_id LIMIT 10.

Pendekatan Paginasi Umum: ORDER BY dan LIMIT

Query SQL yang umum digunakan untuk paginasi daftar record mungkin terlihat seperti ini:

```
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20;
```

Di sini, klausa ORDER BY menyortir hasil berdasarkan kolom created_at (dalam urutan menurun), dan klausa LIMIT serta OFFSET mendefinisikan set baris mana yang akan dikembalikan untuk halaman saat ini.

Masalah Performa dengan OFFSET

Meskipun pendekatan ini berfungsi dengan baik untuk dataset kecil, ia bisa menjadi tidak efisien seiring dengan pertumbuhan jumlah record. Berikut alasannya:

1. Pemindaian Penuh dengan OFFSET: Basis data perlu memindai dan menyortir semua baris untuk menerapkan klausa ORDER BY. Misalnya, jika Anda berada di halaman 100 dengan OFFSET 1000, basis data akan mengambil 1000 baris pertama sebelum membuangnya dan mengembalikan baris 1001–1010. Ini menjadi lambat seiring dengan meningkatnya offset.

2. Waktu Respons yang Meningkat: Seiring dengan meningkatnya offset, waktu untuk mengambil halaman berikutnya juga meningkat. Peningkatan linier dalam waktu eksekusi ini menghasilkan performa yang buruk, terutama untuk dataset besar.

Solusi Paginasi yang Efisien: Menggunakan id < current_id LIMIT 10

Metode alternatif untuk paginasi adalah menggunakan kunci utama (atau kolom terindeks lainnya) untuk menghindari offset yang besar dan penyortiran yang berlebihan. Alih-alih bergantung pada OFFSET, Anda dapat menggunakan ID dari record terakhir pada halaman sebelumnya sebagai titik awal untuk query berikutnya. Metode ini sering disebut "paginasi kunci" atau "paginasi berbasis kursor".

Query yang dioptimalkan terlihat seperti ini:

```
SELECT * FROM posts WHERE id < :last_id ORDER BY id DESC LIMIT 10;
```

Dalam hal ini:

• id adalah kunci utama (atau kolom unik yang terindeks) yang digunakan untuk menyortir hasil.

• :last_id adalah ID dari record terakhir dari halaman sebelumnya, yang memastikan set record berikutnya datang tepat setelah halaman saat ini.

• LIMIT 10 hanya mengambil 10 baris berikutnya, tanpa memindai baris sebelumnya.

Mengapa id < current_id Lebih Efisien

1. Menghindari Pemindaian Penuh: Alih-alih memindai semua baris hingga offset saat ini, basis data hanya melihat baris setelah ID yang diberikan. Ini secara signifikan mengurangi jumlah data yang diproses, terutama pada tabel besar.

2. Performa Terindeks: Jika kolom id terindeks (yang biasanya merupakan kasus untuk kunci utama), eksekusi query sangat cepat karena basis data dapat langsung melompat ke baris yang relevan.

3. Waktu Respons Konsisten: Berbeda dengan pendekatan berbasis OFFSET, di mana performa menurun seiring meningkatnya offset, pendekatan id < current_id memberikan waktu respons yang konsisten terlepas dari seberapa dalam dataset yang Anda telusuri.

Implementasi Contoh dalam Kode

Untuk mengimplementasikan solusi ini, Anda perlu melacak ID dari record terakhir dari halaman sebelumnya. Berikut adalah cara melakukannya dalam aplikasi web yang umum:

Langkah 1: Query halaman pertama

```
SELECT * FROM posts ORDER BY id DESC LIMIT 10;
```

• Ini mengambil 10 post terbaru.

• Misalkan ID dari post terakhir dalam hasil adalah 150.

Langkah 2: Query halaman berikutnya

```
SELECT * FROM posts WHERE id < 150 ORDER BY id DESC LIMIT 10;
```

• Ini mengambil 10 post berikutnya, di mana id kurang dari 150.

• Setelah mengambil ini, Anda menyimpan ID dari record terakhir (misalnya, 140) untuk query halaman berikutnya.

Langkah 3: Ulangi untuk halaman berikutnya Untuk setiap halaman berikutnya, Anda akan melewatkan ID dari record terakhir dari halaman sebelumnya dan terus query dengan id < last_id.

Menangani Kasus Tepi

1. Tidak Ada Lagi Hasil: Ketika tidak ada lagi record yang tersisa (misalnya, jika hasil mengembalikan kurang dari 10 baris), aplikasi dapat mendeteksi bahwa tidak ada halaman lagi yang perlu dimuat.

2. Data Baru Ditambahkan: Jika baris baru ditambahkan sementara pengguna sedang melakukan paginasi, baris baru tersebut akan muncul di halaman-halaman mendatang, menjaga konsistensi.

3. ID yang Hilang (Kesenjangan dalam ID): Meskipun beberapa record dihapus, metode ini tetap bekerja karena hanya melihat baris di mana id < current_id, mengabaikan kesenjangan dalam urutan id.

Contoh Kasus Penggunaan dalam Aplikasi Web

Berikut adalah contoh dasar dalam PHP menggunakan pendekatan ini untuk paginasi posting blog:

```
// Ambil 'last_id' dari permintaan sebelumnya (untuk halaman 2 dan seterusnya)
$last_id = $_GET['last_id'] ?? null;
// Query halaman pertama (tidak ada last_id yang diberikan)
if ($last_id === null) {
    $sql = "SELECT * FROM posts ORDER BY id DESC LIMIT 10";
} else {
    // Query untuk halaman berikutnya
    $sql = "SELECT * FROM posts WHERE id < ? ORDER BY id DESC LIMIT 10";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $last_id);
    $stmt->execute();
}
```

Dalam contoh ini, untuk halaman pertama, query mengambil 10 post terbaru. Untuk halaman-halaman berikutnya, query mengambil post-post di mana id kurang dari ID post terakhir dari halaman sebelumnya.

Kesimpulan

Menggunakan id < current_id LIMIT untuk paginasi adalah alternatif yang sangat efisien dibandingkan dengan query tradisional ORDER BY ... LIMIT OFFSET, terutama dalam aplikasi dengan dataset besar. Dengan menghindari dampak performa dari offset yang besar, metode ini memastikan paginasi yang cepat dan konsisten. Ini memanfaatkan pengindeksan kunci utama dan memastikan pengalaman pengguna yang mulus, bahkan saat melakukan paginasi melalui data dalam jumlah besar.

Pendekatan ini sangat berguna ketika menampilkan konten dengan scroll tak berujung atau ketika kecepatan dan skalabilitas adalah prioritas dalam aplikasi berbasis data besar.