Cara cepat membuat Index di MySQL. :: adakalanya kita perlu membuat index di sebuah table yang berukuran raksasa. Walaupun MySQL terkenal cepat, namun, begitu ukuran data sudah demikian besarnya, maka bahkan untuk sekedar membuat index pun ada triknya.

Kejadian barusan di salah satu client, membuat 1 (satu) buah index saja di sebuah table berukuran 17 GB sampai memakan waktu 12 jam. Padahal, dia perlu membuat 10 buah = 120 jam = 5 hari ! Hanya untuk membuat index.

Dengan cara berikut ini, kita bisa membuat 10 buah index untuk table ukuran 17 GB dengan jumlah data 145.000.000 itu dalam waktu 3 jam saja = peningkatan performa sampai 40x lipat (!)

Berikut rincian detailnya :

=================
USE database_client;

# buat database baru, 
# meng copy structure dari database aslinya
CREATE TABLE table_baru LIKE table_asli;

# ubah menjadi tipe MyISAM = proses pembuatan index nya lebih cepat
ALTER TABLE table_baru ENGINE = MyISAM;

# tambahkan berbagai index yang dibutuhkan
ALTER TABLE table_baru ADD INDEX (nama);
ALTER TABLE table_baru ADD INDEX (tanggal_lahir);
ALTER TABLE table_baru ADD INDEX (tempat_lahir);
ALTER TABLE table_baru ADD INDEX (jenis_kelamin);
ALTER TABLE table_baru ADD INDEX (telpon);
ALTER TABLE table_baru ADD INDEX (id1);
ALTER TABLE table_baru ADD INDEX (id2);
ALTER TABLE table_baru ADD INDEX (id3);
ALTER TABLE table_baru ADD INDEX (id4);
ALTER TABLE table_baru ADD INDEX (id5);

# penting : me non-aktif kan index, 
# sehingga proses INSERT berikut ini bisa berlangsung kencang
ALTER TABLE table_baru DISABLE KEYS;

# sedot data dari table_asli
### 145 juta record = 30 menit
INSERT INTO table_baru SELECT * FROM table_asli;

# aktifkan kembali index,
# maka otomatis MySQL akan mulai 
# membuat semua index yang kita definisikan di atas
### 145 juta record + 10 index = 3 jam
ALTER TABLE table_baru ENABLE KEYS;

### selesai
=================

Sebenarnya, ini saja tidak cukup. Perlu ada optimasi setting MySQL, agar prosesnya jadi bisa dipastikan berjalan cepat. 

Contoh : myisam_sort_buffer_size perlu berukuran cukup besar, agar proses sorting record (dalam membuat index ini) bisa dijalankan di RAM (dan bukannya lari ke disk). Juga, myisam_max_sort_file_size perlu berukuran cukup besar untuk menampung hasil proses sorting.

Setting selengkapnya untuk kondisi ini (145 juta record, 10 buah index) terlampir :

=================
key_buffer              = 256M
max_allowed_packet      = 16M

thread_stack            = 192K
thread_cache_size       = 64

max_connections        = 200
table_cache            = 2000
thread_concurrency     = 16

sort_buffer_size = 256K
join_buffer_size = 16M
read_rnd_buffer_size = 256K

tmp_table_size          = 256M
max_heap_table_size     = 256M

myisam_sort_buffer_size         = 4000M
myisam_max_sort_file_size       = 10000M
=================

Semoga bermanfaat, ketika suatu hari Anda tiba-tiba perlu melakukan hal yang serupa pula 🙂

Bahan rujukan : http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html