Chapter sebelumnya memangkas jumlah query dan lebar data; chapter ini menyerang biaya satu query itu sendiri. Tanpa index, setiap WHERE category_id = 5 memaksa database membaca seluruh tabel baris demi baris — 10.000 baris hari ini, sepuluh juta baris tahun depan. Dengan index yang tepat, database melompat langsung ke baris yang dicari. Kita akan menambahkan index lewat migration, membaca rencana eksekusi dengan EXPLAIN QUERY PLAN, membuktikan bahwa urutan kolom composite index menentukan hidup mati query, dan menimbang jujur biayanya — karena index bukan makanan gratis. Semua pengukuran memakai tabel posts 10.000 baris di SQLite.

Apa Itu Index, Sebenarnya #

Analogi klasiknya: index database adalah indeks buku — mencari “eager loading” tidak dilakukan dengan membaca setiap halaman dari sampul, melainkan membuka indeks lalu melompat ke nomor halamannya. Tanpa indeks, satu-satunya cara adalah membaca seluruh buku.

Realitanya lebih spesifik: kebanyakan index adalah B-tree — struktur terurut berbentuk pohon seimbang yang memetakan nilai kolom ke lokasi barisnya. Pencariannya logaritmik: menggandakan ukuran tabel hanya menambah satu level pohon. Itulah sebabnya query ber-index terasa sama cepatnya di 10 ribu maupun 10 juta baris.

Kolom Mana yang Layak Diindeks #

Index memberi keuntungan pada kolom-kolom yang muncul di posisi kerja query:

  • WHERE — kolom filter (category_id, published, email)
  • JOIN — foreign key di kedua sisi join
  • ORDER BY / GROUP BY — kolom pengurutan, karena B-tree sudah terurut
  • Foreign key — SQLite tidak otomatis membuat index untuk FK, berbeda dengan MySQL

Kolom kardinalitas rendah seperti boolean tunggal (published) biasanya bukan kandidat utama; kombinasi (category_id, published) jauh lebih selektif.

Menambah Index lewat Migration #

Index didefinisikan di migration agar terdokumentasi bersama skema. Kondisi awal kita: posts.category_id belum terindeks. Buktikan dulu masalahnya:

php 
use Illuminate\Support\Facades\DB;

$r = DB::select("explain query plan select id from posts where category_id = 5");
echo $r[0]->detail, "\n";

Output yang Dihasilkan:

text 
SCAN posts

SCAN berarti SQLite membaca seluruh 10.000 baris — terukur 4,49 ms hanya untuk satu filter. Sekarang migration-nya:

php 
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('posts', function (Blueprint $table) {
            $table->index('category_id');
        });
    }

    public function down(): void
    {
        Schema::table('posts', function (Blueprint $table) {
            $table->dropIndex(['category_id']);
        });
    }
};
bash 
php artisan migrate

Output yang Dihasilkan:

text 
INFO  Running migrations.

2026_10_13_222244_add_category_index_to_posts_table ........... 11.28ms DONE

Satu berkas migration selesai dalam 11,28 ms pada data yang sudah ada — index dibangun tanpa mengosongkan tabel. Hasil setelahnya:

php 
$r = DB::select("explain query plan select id from posts where category_id = 5");
echo $r[0]->detail, "\n";

Output yang Dihasilkan:

text 
SEARCH posts USING COVERING INDEX posts_category_id_index (category_id=?)

SCAN menjadi SEARCH ... (category_id=?) — database melompat tepat ke entri yang cocok. Waktu query turun dari 4,49 ms ke 0,07 ms: 64 kali lebih cepat dengan 11 ms kerja sekali jalan, dan jurangnya melebar seiring tabel membesar. Inilah tuas optimasi dengan rasio untung tertinggi di seluruh section ini.

Composite Index: Urutan Kolom Menentukan #

Query nyata sering memfilter dua kolom sekaligus, dan index gabungan (composite) harus mengikuti pola querynya. Aturan praktisnya: kolom equality (=) paling selektif di posisi terdepan. Eksperimen berikut memakai index (published, category_id) untuk query yang hanya memfilter category_id, dengan helper kecil untuk mengambil waktu tercepat dari beberapa percobaan:

php 
// helper pengukuran: waktu tercepat dari 10 percobaan
function ukur(callable $fn, int $ulang = 10): float
{
    $terbaik = INF;
    for ($i = 0; $i < $ulang; $i++) {
        $mulai = hrtime(true);
        $fn();
        $terbaik = min($terbaik, (hrtime(true) - $mulai) / 1e6);
    }
    return $terbaik;
}

DB::statement('CREATE INDEX posts_pub_cat ON posts (published, category_id)');
$r = DB::select("explain query plan select id from posts where category_id = 5");
echo $r[0]->detail, "\n";
echo sprintf("count: %5.2f ms\n",
    ukur(fn () => DB::table('posts')->where('category_id', 5)->count()));
DB::statement('DROP INDEX posts_pub_cat');

DB::statement('CREATE INDEX posts_cat_pub ON posts (category_id, published)');
$r = DB::select("explain query plan select id from posts where category_id = 5");
echo $r[0]->detail, "\n";
echo sprintf("count: %5.2f ms\n",
    ukur(fn () => DB::table('posts')->where('category_id', 5)->count()));

Output yang Dihasilkan:

text 
SEARCH posts USING COVERING INDEX posts_pub_cat
count:  0.47 ms
SEARCH posts USING COVERING INDEX posts_cat_pub (category_id=?)
count:  0.11 ms

Dengan (published, category_id), SQLite tidak bisa mencari berdasarkan kolom kedua — hanya mampu memindai keseluruhan index (0,47 ms). Lebih baik daripada scan tabel, tetapi bukan lompatan. Index (category_id, published) mengembalikan SEARCH (category_id=?) dengan 0,11 ms. Prinsip awalan kiri: index multi-kolom hanya bisa “dicari” bila kolom terdepannya muncul di kondisi query.

Covering Index dan Unique #

Dua varian penting melengkapi koleksi index kita. Pertama, covering index: query yang seluruh kolomnya — filter maupun hasil — hidup di dalam index tidak perlu menyentuh tabel sama sekali. Query select id, judul from posts where judul = ... membuktikannya, sebelum dan sesudah CREATE INDEX posts_judul_index ON posts (judul):

text 
tanpa index judul: SCAN posts
dengan index judul: SEARCH posts USING COVERING INDEX posts_judul_index (judul=?)

Kata COVERING menandakan index memuat semua yang dibutuhkan query: nilai judul untuk mencari dan id (rowid) yang melekat di tiap entri. Tabel dilompati total — bentuk paling irit dari penggunaan index.

Kedua, unique constraint adalah index. Tabel users bawaan punya $table->string('email')->unique(), dan buktinya terlihat:

php 
use Illuminate\Support\Facades\DB;

foreach (DB::select("pragma index_list('users')") as $i) {
    echo sprintf("  %-30s unique=%s\n", $i->name, $i->unique ? 'ya' : 'tidak');
}
$r = DB::select("explain query plan select * from users where email = '[email protected]'");
echo $r[0]->detail, "\n";

Output yang Dihasilkan:

text 
  users_email_unique             unique=ya
SEARCH users USING INDEX users_email_unique (email=?)

Lookup email login melompat lewat users_email_unique tanpa perlu index tambahan — keunikan sekaligus kecepatan dalam satu deklarasi.

Biaya Over-Indexing #

Index bukan makanan gratis: setiap INSERT/UPDATE/DELETE harus memperbarui semua index tabel. Kita ukur di tabel events — 20.000 baris disisipkan dengan 0 index, lalu 20.000 baris lagi setelah 5 index dibuat:

text 
insert 20.000 baris (0 index):    118 ms
insert 20.000 baris (5 index):    231 ms

Kecepatan tulis terpangkas hampir dua kali lipat, dan ruangnya ikut membengkak: berkas database yang sama berisi 40.000 baris tumbuh dari 5,41 MB menjadi 9,04 MB setelah 5 index ditambahkan — biaya penyimpanan 67% tambahan. Kesimpulannya bukan “jangan bikin index”, melainkan indekslah kolom yang benar-benar dipakai query, dan hapus index yang sudah tidak melayakan query mana pun.

Index pada Kolom JSON #

Kolom JSON di SQLite tidak bisa diindeks secara langsung, tetapi expression index bisa menampung hasil json_extract(). Contoh pada tabel products dengan kolom attributes:

php 
use Illuminate\Support\Facades\DB;

$r = DB::select("explain query plan select id from products
    where json_extract(attributes, '\$.warna') = 'merah'");
echo "tanpa index: ", $r[0]->detail, "\n";

DB::statement("CREATE INDEX products_warna_index
    ON products (json_extract(attributes, '\$.warna'))");

$r = DB::select("explain query plan select id from products
    where json_extract(attributes, '\$.warna') = 'merah'");
echo "dengan expression index: ", $r[0]->detail, "\n";

Output yang Dihasilkan:

text 
tanpa index: SCAN products
dengan expression index: SEARCH products USING INDEX products_warna_index (<expr>=?)

Syaratnya ketat: ekspresi di query harus persis sama dengan ekspresi di index. Di MySQL dan PostgreSQL, padanannya adalah generated column + index atau operator JSON khusus.

Index membuat query database gesit. Namun sebagian beban tidak perlu sampai ke database: hasil komputasi yang mahal bisa disimpan sementara di cache — strategi chapter Application Caching .