17 December 2006

INDEX

Setiap orang yang menggeluti SQL pasti akan bertemu dengan INDEX. Apa dan bagaimana yang dimaksud dengan INDEX? Dari hasil googling dengan keyword "define:index" (tanpa tanda petik) akan langsung ketemu banyak definisi dari INDEX yang tersebar di internet (catatan: "define:" adalah kata kunci spesial yang dapat digunakan untuk mencari berbagai definisi menggunakan google), namun dari hasil yang banyak tersebut saya memilih definisi yang berikut ini:

A special database object that lets you quickly locate particular records based on key column values. Indexes are essential for good database performance.
www.orafaq.com/glossary/faqglosi.htm
Ada 3 cara untuk membuat INDEX didalam database MySQL:
  1. Pada saat CREATE TABLE
    Kita dapat langsung menyisipkan syntax untuk membuat index, misalnya:
    CREATE TABLE person (
    nomor_id int(10) unsigned NOT NULL default '0',
    nama char(100) NOT NULL default '',
    INDEX nomor_id_idx (nomor_id)
    );
  2. Dengan perintah CREATE INDEX
    Dengan cara ini, tabel pada mulanya di-CREATE tanpa INDEX, kemudian index dibuat belakangan dengan perintah CREATE INDEX:
    CREATE INDEX nomor_id_idx ON person (nomor_id);
    Syntax-nya secara empiris dapat ditulis:
    CREATE INDEX nama_index ON nama_tabel (nama_kolom, ...);
  3. Dengan perintah ALTER TABLE
    Hampir mirip dengan CREATE INDEX, index ini dibuat belakangan, dengan perintah:
    ALTER TABLE person ADD INDEX nomor_id_idx (nomor_id);
    Dan bentuk empirisnya:
    ALTER TABLE nama_tabel ADD INDEX nama_index (nama_kolom, ...);
Dari ketiga cara di atas, terserah kepada kita untuk menggunakan yang mana, semuanya sama saja.

Sedangkan untuk menghapus index menggunakan perintah:
ALTER TABLE nama_tabel DROP INDEX nama_index;

Setelah index dibuat, lalu bagaimana cara menggunakannya? Penggunaan index didalam query biasanya terjadi secara otomatis oleh database engine-nya, namun otomatisasi tersebut dapat kita arahkan atau kendalikan, yaitu dengan panduan atau patokan sebagai berikut: Yaitu index akan digunakan:
  • Pada klausa WHERE
    Maka kemungkinan kita perlu untuk membuat index pada setiap kolom yang tersebut didalam klausa WHERE. Misalnya:
    SELECT * FROM person WHERE nama = 'John';
    Jika jumlah record hanya sedikit, INDEX tidak begitu berpengaruh. Namun jika record sudah mencapai puluhan ribu bahkan ratusan ribu, maka kita perlu membuat INDEX untuk kolom nama:
    CREATE INDEX nama_idx ON person (nama(10));
    Perintah diatas akan membuat index untuk kolom nama namun hanya menggunakan 10 karakter pertama saja. Dengan hanya 10 karakter saja, index menjadi lebih ramping dan dapat diakses lebih cepat. Teknik ini cukup optimal dibandingkan dengan membuat index seluruh panjang data kolom (100 karakter) yang mengakibatkan ukuran index menjadi sangat besar dan lambat. Namun untuk mengetahui ukuran optimalnya, apakah 10 atau 15 atau 50 karakter yang akan di-index, kita harus melihat karakteristik datanya, misalnya untuk nama orang - 10 karakter sudah cukup menjadi pembeda.

  • Pada klausa JOIN:
    SELECT a.id_pasien,b.nama
    FROM pasien a
    LEFT JOIN person b ON b.nomor_id = a.nomor_id
    Maka kita perlu membuat index untuk kolom nomor_id pada tabel person (sudah kita buat untuk contoh awal). Mengapa yang perlu dibuat adalah index untuk kolom nomor_id pada tabel person, bukan pada tabel pasien? Karena pencarian berdasarkan kolom nomor_id hanya akan dilakukan pada tabel person saja, dan pencarian inilah yang akan menggunakan index (jika ada).

  • Pada klausa ORDER BY
    SELECT * FROM person ORDER BY nama;
    Query tersebut akan mengurutkan hasil query berdasarkan nama. Sedangkan sifat alamiah dari index ialah berurutan, maka query tersebut akan sangat terbantu jika sudah ada index pada kolom tersebut.

  • Pada klausa GROUP BY
    SELECT COUNT(*) FROM person GROUP BY usia;
    Hampir sama dengan ORDER BY, jika ada index pada kolom usia, maka proses query akan menggunakannya.

Dengan memahami karakteristik tersebut diatas maka barulah kita dapat merancang query yang optimal dan efisien. Terutama saya, pada query dengan JOIN, kadang kala saya lupa untuk membuat index, setelah query bertambah rumit baru dirasa lambat sekali. Kemudian setelah dibuat index untuk semua kolom yang tersebut didalam klausa JOIN, barulah query dapat selesai dalam waktu sekejap.

3 comments:

Adityo said...

hellow mister adiet9000.... ternyata sdh punya blog ya skrg... apa kabar nih?

jangan lupa main ke blog ku, dan blog bunda lala di prestise.blogspot.com.

hahaha...
keep contact ya.

Sunya said...

Pak Adiet mau tanya nih, saya pelaku simkes di tingkat puskesmas.. mohon penjelasan tentang software yang memungkinkan bagi kami2 sampai dinkes kab! Bagaimana bila kami mau mengembangkan simkes berbasis MS Access, bersifat open! bagaimana kelebihan dan kekurangannya.. apa layak dan bisa bertahan dalam perkembangan SIMKES secara nasional fiture?? mohon tanggapan (hum.yusuf@gmail.com)

Adiet said...

Sebenarnya Ms Access cukup bagus dan mudah untuk dideploy, namun beberapa referensi yang pernah saya baca di Internet mengatakan bahwa Ms Access tidak didesain untuk skala besar. Silakan coba search google (googling) dengan keywords: "ms access limitation", maka ada beberapa petunjuk yang bisa dijadikan acuan. Bukan saya mengatakan tidak cocok, tapi tergantung bagaimana aplikasinya dikembangkan dan dasar kebutuhan yang akan dipenuhi diperhitungkan.

Untuk software yang memungkinkan kami kira cukup banyak alternatif, misalkan dengan c++, vb, delphi, php, ruby, dll.

Pada prinsipnya silakan pakai apa saja, asalkan dibuat dengan apik dan dibuat dengan keunggulan tersendiri dan spesifik (bukan cuma mengikuti standar), pasti bagus. Good luck.