Cara menggunakan mysql index

Bagi Anda yang sering main-main dengan MySQL atau database secara umum, tentunya tidak asing dengan istilah PRIMARY KEY, UNIQUE dan INDEX. Akan tetapi ternyata banyak yang belum mengetahui tentang ketiga hal tersebut. Apa perbedaan PRIMARY KEY dan UNIQUE? Kapan kita gunakan PRIMARY KEY, UNIQUE dan INDEX ketika membuat sebuah tabel? Beberapa pertanyaan tersebut banyak yang belum tahu, atau bahkan baru mendengar kali ini ya? he 3x 🙂

Dalam artikel kali ini, saya akan mencoba membahas secara tuntas apa maksud dengan kedua istilah di atas, disertai contoh penerapannya sehingga tidak terkesan teoritis.

Pertama-tama kita bahas dahulu tentang apa itu INDEX dalam database. Index ini sangat bermanfaat sekali untuk proses pencarian data (data seeking). Jika suatu tabel tidak ada index, maka proses pencarian data bisa membutuhkan waktu yang lama (untuk data dalam jumlah besar). Analoginya adalah misalkan kita akan mencari sebuah nomor telepon seseorang bernama ‘Rosihan Ari Yuana’ dalam buku telepon. Apabila dalam buku tersebut tidak ada index, maka kita akan kesulitan mencarinya bahkan kita akan mencarinya di setiap halaman buku. Namun dengan adanya index, kita tidak perlu mencari di setiap halaman buku telepon namun cukup mencarinya lewat index dari pemilik nomor telepon yang diawali dengan huruf ‘R’. Dengan begitu proses akan lebih cepat bukan? Demikian juga dengan database. Kita bisa men-setting suatu field menjadi index supaya proses pencarian data menjadi lebih cepat. Bagaimana caranya? Untuk menset suatu field sebagai index, cukup dengan query sbb:

ALTER TABLE `namatabel` ADD INDEX (`namafield`);

Field bagaimana yang bisa diset sebagai INDEX? Kita bisa mengeset field tertentu sebagai INDEX apabila field tersebut digunakan sebagai kriteria dalam proses searching data, dalam hal ini field yang digunakan dalam klausa WHERE pada query SQL nya.

Catatan: Sebuah tabel bisa terdapat lebih dari satu field yang diset sebagai index

Selanjutnya mengenai PRIMARY KEY dan UNIQUE. Secara fungsi atau kegunaan, PRIMARY KEY dan UNIQUE keduanya sama-sama digunakan untuk menjamin bahwa tidak ada duplikasi data pada field tertentu. Selain itu, keduanya sama-sama digunakan untuk keperluan indexing. Dengan kata lain, field yang diset sebagai Primary Key atau Unique otomatis dia juga sudah diset sebagai index, sehingga kita tidak perlu mengeset secara manual sebagai index lagi.

Meskipun begitu, terdapat perbedaan di antara keduanya. Suatu field yang diset sebagai Primary Key, data record pada field tersebut tidak diperbolehkan NULL. Oleh karena itu, jika Anda mengeset suatu field sebagai Primary Key maka secara otomatis muncul ‘NO’ pada kolom ‘NULL’ di struktur tabelnya. Hal ini bisa Anda lihat di phpMyAdmin. Perhatikan contoh tabel ‘mahasiswa’ yang terdiri dari field: NIM, NAMAMHS, ALAMAT dan NOSIM di bawah ini.

Keterangan:
Pada gambar di atas, field ‘NIM’ bisa diset sebagai Primary Key. Dalam kasus riilnya, seorang mahasiswa pasti memiliki NIM (nomor induk mahasiswa), NIM setiap mahasiswa pasti unik (tidak ada yang sama) serta tidak mungkin seorang mahasiswa tidak memiliki NIM, sehingga dalam database mahasiswa ini, NIM kita bisa set sebagai Primary Key (karena NIM tidak boleh NULL).

Bagaimana untuk mengeset suatu field sebagai Primary Key? Ini dia query nya:

ALTER TABLE `namatabel` ADD PRIMARY KEY (`namafield`);

Sedangkan UNIQUE, berbeda dengan Primary Key, data pada field yang diset sebagai Unique dapat berupa NULL.

Contoh penerapan dari UNIQUE ini misalkan dari tabel ‘mahasiswa’ di atas, adalah field ‘NOSIM’ bisa diset sebagai UNIQUE. Mengapa NOSIM bisa diset sebagai UNIQUE? Ya.. karena No SIM itu tidak ada yang sama atau bersifat unik, namun tidak semua orang khususnya mahasiswa dalam hal ini memiliki SIM. Sehingga untuk NOSIM ini dimungkinkan NULL atau tidak diisi. Karena field NOSIM ini diset sebagai UNIQUE, bila ada data No. SIM yang akan dientri sama (duplikat) maka proses entri ini gagal.

Bagaimana cara mengeset suatu field sebagai UNIQUE? Ini dia query nya:

ALTER TABLE `namatabel` ADD UNIQUE (`namafield`);

Catatan: Sebuah tabel dapat terdiri dari beberapa field yang UNIQUE.

OK sudah ada pencerahan kan apa itu PRIMARY KEY, UNIQUE dan INDEX? Selain dengan menggunakan query di atas, cara untuk menambahkan atau menghapus Primary Key, Unique dan Index bisa juga dilakukan dengan mudah di phpMyAdmin karena sudah ada fitur untuk itu.

Salah satu penyebab aplikasi menjadi lambat bisa diakibatkan oleh jumlah record pada database yang membengkak. Jumlah record yang banyak apalagi sudah menyentuh jutaan akan mempengaruhi performa query terutama pada saat pencarian. Penambahan Index pada column MySQL dapat membuat query pencarian menjadi lebih cepat.

Apa itu Index ?

Secara umum, indeks adalah suatu daftar kata – kata yang diurutkan disertai dengan rujukan atau referensi ke halaman yang menyebutkan. Prinsip Index pada MySQL sama persis dengan yang ada pada buku pada umumnya. Index pada MySQL berisi pointer ke data yang berelasi sehingga dapat membuat query pencarian menjadi lebih cepat.

Mempercepat Pencarian Data

Index dapat mempercepat pencarian pada kolom yang digunakan pada klausa WHERE. Query dapat menjadi lebih cepat karena dengan adanya Index MySQL tidak perlu melakukan Full Table Scan atau mebandingkan keseluruhan data untuk menemukan data yang sesuai.

Penambahan index akan sangat terasa manfaatnya pada table memiliki banyak record dan sering dilakukan query pencarian. Pada kasus saya dengan tabel yang memiliki 1.5 juta record, kecepatan query pencarian bisa berubah dari 0.5 detik menjadi 0.0003 detik setelah menambahkan Index pada salah satu kolom nya.

Kolom yang Di Index

Index dapat ditambahkan pada setiap column pada table, namun penggunaan Index yang berlebihan akan berdampak pada performa query saat melakukan INSERT, UPDATE, dan DELETE karena INDEX juga perlu diperbaharui. Oleh sebab itu sangat penting untuk menentukan mana kolom yang dirasa sangat perlu di Index. Biasanya Index ditambahkan pada kolom yang sering dijadikan acuan dalam klausa query WHERE. Contohnya apabila anda memiliki table seperti dibawah ini:

id, kode_pendaftaran, nama, tanggal_lahir, sekolah_asal

Bila sering dilakukan query yang mencari data berdasarkan kode_pendaftaran dan nama, maka tambahkan Index pada kedua kolom tersebut. Sederhananya seperti itu, informasi mengenai jenis – jenis index dapat anda baca selengkapnya pada dokumentasi MySQL: How MySQL Uses Indexes

Untuk membuat Index pada salah satu kolom dapat digunakan query dibawah ini:

ALTER TABLE `nama_tabel` ADD INDEX(`nama_field`)

Sementara untuk menghapus index dapat digunakan query:

ALTER TABLE `nama_tabel` DROP INDEX `nama_field`;

query diatas dapat digunakan untuk Single Column Index yang sederhana, apabila anda membutuhkan Multi-column index dengan konfigurasi lanjutan dapat dilihat pada dokumentasi MySQL : Create Index Statement

apabila anda pengguna phpMyAdmin maka pembuatan index akan lebih mudah dilakukan. Pilih table yang akan ditambahkan index, kemudian klik menu tab Structure, kemudian klik menu Lainnya pada kolom yang akan diberi index.

Menemukan Query yang Lambat

Query yang lambat pada umumnya akan membutuhkan waktu lama untuk selesai. Untuk menemukan query yang lambat anda dapat memeriksa prosess mysql dengan query:

show processlist;

Hasil outputnya kurang lebih seperti dibawah ini:

+-----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 | Id  | User | Host            | db   | Command | Time | State        | Info                                                                                                 | Progress |
 +-----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 |  70 | root | localhost       | NULL | Query   |    0 | starting     | show processlist                                                                                     |    0.000 |
 | 359 | root | localhost:50176 | test | Query   |    2 | Sending data | SELECT *  FROM table WHERE code LIKE 'xx' AND deleted_at IS NOT NULL                             |    0.000 |
 | 360 | root | localhost:50177 | NULL | Sleep   |    1 |              | NULL                                                                                                 |    0.000 |
 +-----+------+-----------------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
 3 rows in set (0.000 sec)

Cari proses yang memiliki Time paling lama dari hasil query tersebut, kemudian perhatikan di bagian kanan ada Info pendek mengenai query yang sedang dikerjakan. Analisa dengan baik query tersebut sebelum memutuskan untuk menambahkan index.

Apa itu Index di MySQL?

Apa itu Index DB?

Mengapa index pada database diperlukan?

Langkah langkah membuat database di MySQL?