Cara menggunakan indexing in mysql example

Semua software developer pasti pernah menggunakan salah satu fitur di database bernama “index”. Belum pernah mencoba?? Hmm,, paling tidak pernah mendengarnya. Belum juga pernah dengar??? Ya paling tidak sekarang sudah dengar dari paragraf pertama artikel ini hehe.

Artikel ini akan menjelaskan apa itu index dan bagaimana index bekerja secara general, sebagai salah satu teknik dalam mengoptimalkan database. Bahasan ini tidak bergantung pada salah satu brand database, tapi kita akan fokus ke penggunaan salah satu jenis database, yaitu Relational DB Management System (RDBMS) agar lebih mudah dimengerti. Jadi, jika belum tahu konsep RDBMS, silahkan pelajari artikel lain terlebih dahulu.

Pendahuluan

Table

Table di RDBMS berfungsi untuk menyimpan kumpulan data (dataset) dengan jenis & skema yang sama. Seperti gambar di bawah, ini merupakan contoh table dengan nama m_customers untuk menyimpan data diri customer sebuah website.

Table m_customers

Query

Sedangkan query adalah metode pengambilan data dari satu atau lebih table. Di RDBMS, kita mengenal SQL (Structured Query Language) sebagai “bahasa” yang terstruktur untuk melakukan query.

Perhatikan SQL statement di bawah ini :

Select * From m_customers Where lastname =  'Dare'

Apa yang sebenarnya terjadi jika SQL statement di atas dijalankan?

Ilustrasi proses query pada table m_customers

Secara sederhana, ketika SQL statement di atas dijalankan, maka RDBMS akan melakukan eksplorasi seluruh data di table m_customers , dari data pertama sampai terakhir dan melakukan filter (mencari data dengan lastname = ‘Dare’) di setiap baris. Baris data yang memenuhi persyaratan akan dikumpulkan sebagai hasil.

Problem

Problem #1 : Data Size

Ilustrasi grafik response time v.s data volume

Dengan data yang relatif kecil proses query terlihat sangat cepat. Tapi, dengan metode sederhana diatas, apakah proses query akan memerlukan waktu (query time) yang sama jika jumlah baris yang perlu dieksplor menjadi berkali — (ratusan bahkan juataan)-kali lipat ? Tentu tidak. Query time (response time) akan berbanding lurus dengan besar data.

Semakin besar data, semakin besar query time-nya.

Problem #2 : Data is unsorted

Pada dasarnya, data yang disimpan pada database adalah tidak berurutan (unsorted). Ilustrasi di bawah akan menjelaskan bagaimana proses pencarian data menjadi tidak efektif jika data tidak berurutan :

Terdapat dua (2) dataset yang memiliki ukuran dan data yang sama, yaitu 12 bilangan bulat. Bedanya, dataset-1 tidak berurutan sedangkan dataset-2 berurutan.

Dataset-1 : Unsorted

2,1,3,4,1,5,1,7,9,8,4,6

Dataset-2 : Sorted

1,1,1,2,3,4,4,5,6,7,8,9

Pada dataset-1 kita membutuhkan 12 langkah untuk mencari bilangan 5 (lima). Pencarian harus dilakukan dari awal sampai akhir untuk memastikan tidak ada lagi angka 5 yang tersisa (meskipun angka 5 hanya ada satu dan posisinya berada di tengah-tengah dataset).

Sedangkan pada dataset-2, hanya dibutuhkan 9 langkah pencarian. Berawal dari angka 1 dan berhenti di angka 6.

Kenapa bisa begitu? karena kita tidak perlu melanjutkan pencarian jika sudah tahu kalau angka selanjutnya pasti lebih besar.

Problem 3: Disk I/O

Data MYSQL tersimpan di file fisik pada harddisk komputer.

Seluruh data pada database tersimpan di harddisk. Gambar berikut menunjukkan bagaimana data pada database MySQL tersimpan.

Seperti yang kita ketahui, bahwa salah satu cost pada komputasi yang paling besar adalah disk I/O (lihat gambar di bawah). Proses menulis / membaca sebuah file di harddisk membutuhkan waktu & resource yang besar.

Jika proses pencarian data dilakukan dengan membaca file secara langsung, maka bisa dibayangkan berapa lama query time yang dibutuhkan untuk ukuran data yang besar.

Ilustrasi perbandingan cost komputasi.

Dari ke tiga masalah di atas, kita bisa menyimpulkan bahwa proses query sederhana di atas akan menjadi sangat tidak efektif jika problem-problem di atas tidak bisa diselesaikan dengan cara yang lebih optimal.

Solusi : Index

RDBMS sudah memiliki built-in solution untuk problem di atas, yaitu fitur Index.

Apa itu Index?

Buku yellowpages

Masih ingat buku kuning besar “Yellowpages” jika kita berlangganan telpon rumah? Atau pernah buka kamus bahasa? Yap, kita tidak perlu susah-susah menelusuri kata per kata di seluruh buku untuk mencari sebuah konten. Cukup telusuri “tanda” yang disediakan di pojok / samping halaman, kita akan diarahkan ke halaman yang konten-nya dimulai dengan huruf atau kata sesuai “tanda” tersebut. Begitulah penjelasan sederhana bagaimana index bekerja.

Database Index

Pada database, index merupakan sebuah struktur data yang berisi kumpulan keys beserta referensinya ke actual data di table. Tujuannya untuk mempercepat proses penentuan lokasi data tanpa melakukan pencarian secara penuh ke seluruh data (full scan).

Benefit

Fitur ini bisa menjawab 3 problem yang disampaikan sebelumnya dengan benefit-benefit berikut :

1. Sorted & Less

Jumlah data pada index jauh lebih kecil daripada data aslinya (tergantung kondisi datanya). Dan data-data ini juga tersimpan secara berurutan dalam bentuk struktur apapun (bisa linear list, tree, dsb). Tentu ini menjawab problem #1 & problem #2.

Dengan index, pencarian dimulai dengan menelusuri data pada index (yang jauh lebih kecil & terurut), kemudian index akan memberikan referensi ke posisi data yang asli. Dari posisi tersebut, pencarian pada dataset asli akan menjadi lebih singkat.

Ilustrasi index pada database | Source : Wikipedia

2. (Mostly) Stored in Memory (RAM)

Dengan ukuran dataset yang jauh lebih kecil, index besar kemungkinan dapat disimpan di RAM, dimana cost untuk baca/tulis pada RAM jauh lebih kecil daripada pada Harddisk. Sehingga tidak bisa dipungkiri proses pencarian keys pada index sangatlah cepat.

Eksperimen

Pada section ini, saya melakukan eksperimen kecil untuk menunjukkan performa query pada table dengan index dan tanpa index.

Dataset

Data Size : 2.904.824 records.

Skema table seperti gambar dibawah ini :

Spesifikasi Komputer & Software

• Macbook Pro Retina Core I-5
• RAM 8G
• SSD 256 GB
• MySQL RDBMS

Indexes

Saya membuat 3 tables dengan skema & data yang sama, tetapi memiliki index yang berbeda :

• Table student | indexes = id (PK)
• Table student_index_gender | indexes = id (PK),

  2,1,3,4,1,5,1,7,9,8,4,6

  1
• Table

  2,1,3,4,1,5,1,7,9,8,4,6

  2 | indexes = id (PK),

  2,1,3,4,1,5,1,7,9,8,4,6

  4, lastname

3 tables dengan index yang berbeda.

Index Size

Gambar di atas menunjukkan besar data asli & index di setiap table. Informasi di atas dapat dihasilkan dengan query berikut :

SELECT table_name AS ‘Table’, 
round(((data_length) / 1024 / 1024), 2) ‘Data Size in MB’ ,
round(((index_length) / 1024 / 1024), 2) ‘Index Size in MB’ ,
round(((data_length + index_length) / 1024 / 1024), 2) ‘Total Size in MB’
FROM information_schema.TABLES
WHERE table_schema = “YOUR_TABLE_NAME”;

Eksperimen #1

Pada ekseperimen ini saya menjalankan sebuah query sederhana dengan where statement pada kolom

2,1,3,4,1,5,1,7,9,8,4,6

1. Seperti yang terlihat pada gambar di bawah, table student_index_gender yang memiliki index pada kolom

2,1,3,4,1,5,1,7,9,8,4,6

1 memiliki query time yang jauh lebih cepat.

Eksperimen #2

Pada ekseperimen ini saya menjalankan sebuah query sederhana dengan where statement pada kolom lastname. Seperti yang terlihat pada gambar di bawah, table

2,1,3,4,1,5,1,7,9,8,4,6

2 yang memiliki index pada kolom

2,1,3,4,1,5,1,7,9,8,4,6

4&lastname memiliki query time yang jauh lebih cepat dari table lainnya.

Kesimpulan

Fitur index sangat membantu dalam mengoptimalkan proses query, terutama dengan data yang besar. Satu-satunya additional cost pada penggunaan fitur index adalah bertambahnya ukuran data secara total, karena index memiliki ukuran data tersendiri , meskipun relatif jauh lebih kecil daripada data asli.

Semoga artikel ini bermanfaat bagi kita dalam memahami bagaimana index pada database bekerja. Silahkan sampaikan pendapatmu di kolom komentar jika ada saran/kritik maupun tambahan informasi. Terimakasih.

Ohya, kalau kamu tertarik bekerja dengan teknologi semacam ini, silahkan buka website career DOT Indonesia https://career.dot.co.id. Kami sedang membuka lowongan untuk beberapa posisi software developer. Join with us!

Apa kegunaan dari index di tabel MySQL?

Indeks adalah tabel pencarian khusus yang dapat digunakan mesin pencari basis data untuk mempercepat pengambilan data.

Apa itu Index di phpmyadmin?

Index adalah sebuah objek dalam sistem database yang dapat mempercepat proses pencarian (query) data. Saat database dibuat tanpa menggunakan index, maka kinerja server database dapat menurun secara drastis.

Apa kegunaan dari index di tabel?

Fungsi INDEX menghasilkan nilai atau referensi ke nilai dari dalam tabel atau rentang.

Langkah langkah membuat database di MySQL?

Untuk membuat database MySQL baru, masuk cPanel dan klik menu MySQL Databases:.

Pilih nama database. Prefix defaultnya adalah "yoururser_". ... .

Scroll ke bawah dan cari Add New User. Masukkan username dan password, lalu klik Create User:.

Cari opsi Add User To Database untuk menghubungkan User ke Database. ... .

Selesai!.