สร้าง index ในฐานข้อมูล MySQL

จากที่ตั้งใจไว้ตั้งแต่แรกคือเมื่อเจอประสบการณ์ทำงานอะไร ที่น่าจะเป็นประโยชน์แก่ผู้อื่น จะนำมาเขียนเล่าสูกันฟัง วันนี้ขอแนะนำการเพิ่มความเร็วในการค้นหาข้อมูลจากฐานข้อมูล MySQL เพียงแค่พิมพ์คำสั่งเดียวเท่านั้น เพื่อสร้าง index ของฟิลด์ที่ใช้ในการค้นหา

คำเตือน! การทดสอบแนะนำให้ทำบนเครื่องทดลองเท่านั้น เมื่อได้ผลลัพธ์ที่ต้องการแล้ว ค่อยไปปรับใช้กับเครื่องจริง

ฐานข้อมูลที่ทดลองปรับปรุง ใช้เก็บข้อมูลสินค้าของบริษัท ปัญหาที่พบคือเวลาค้นหาชื่อสินค้าโดยพิมพ์ชื่อแล้วกดค้นหา ต้องรอสักพักกว่าจะขึ้น ทั้งๆ ที่มีจำนวน record ประมาณ 60,000 แถว เท่านั้น เมื่อตรวจสอบรายละเอียดของ table แล้ว จะเป็นประมาณนี้

CREATE TABLE `items` (
  `item_id`       int(10) unsigned NOT NULL auto_increment,
  `item_name`     varchar(255) default NULL,
  `item_descr`    varchar(255) default NULL,
  `item_barcode`  varchar(255) default NULL,
  `item_total`    int(11) default '0',
  `item_status`   char(1) default NULL,
  PRIMARY KEY  (`item_id`)
);

เมื่อทดสอบการค้นหาข้อมูล โดยใช้คำสั่ง SELECT จะได้ผลลัพธ์ดังนี้

mysql> SELECT item_id,item_name FROM items WHERE item_name='PNGS932A-31';

+---------+----------------------+
| item_id | item_name            |
+---------+----------------------+
|   22434 | PNGS932A-31          |
+---------+----------------------+
1 row in set (0.17 sec)

จากตัวอย่าง ใช้เวลาในการค้นหา 0.17 วินาที

เพื่อเพิ่มความเร็วในการค้นหาฟิลด์ที่ใช้งานบ่อยๆ แนะนำให้ทำ index หรือ key กับฟิลด์นั้นๆ วิธีการคือใช้คำสั่ง ALTER TABLE ตามตัวอย่างดังนี้

mysql> ALTER TABLE items ADD KEY (item_name);
Query OK, 57625 rows affected (1.34 sec)
Records: 57625  Duplicates: 0  Warnings: 0

แล้วทดลองค้นหาโดยใช้คำสั่ง SELECT อีกครั้งหนึ่ง เพื่อเปรียบเทียบ

mysql> SELECT item_id,item_name FROM items WHERE item_name='PNGS932A-31';

+---------+----------------------+
| item_id | item_name            |
+---------+----------------------+
|   22434 | PNGS932A-31          |
+---------+----------------------+
1 row in set (0.01 sec)

ผลลัพธ์ที่ได้ จะใช้เวลาแค่ 0.01 วินาที เท่านั้น ซึ่งเร็วกว่ามาก เมื่อเปรียบเทียบกับการไม่สร้าง index

ทดสอบเอา index ออกอีกครั้ง แล้วทดสอบ SELECT อีกที ผลลัพธ์จะกลับมาช้าเหมือนเดิม

ตัวอย่างการเอา index ออกจากฟิลด์ item_name

mysql> ALTER TABLE items DROP KEY item_descr;
Query OK, 57625 rows affected (0.70 sec)
Records: 57625  Duplicates: 0  Warnings: 0

mysql> SELECT item_id,item_name FROM items WHERE item_name='PNGS932A-31';

+---------+----------------------+
| item_id | item_name            |
+---------+----------------------+
|   22434 | PNGS932A-31          |
+---------+----------------------+
1 row in set (0.17 sec)

คำแนะนำ

• ทำ index กับฟิลด์ที่ใช้ค้นหาบ่อยๆ หรือใช้เป็นเงื่อนไขการ JOIN TABLE
• ไม่จำเป็นเสมอไป หลังจากทำ index แล้วจะเร็วขึ้น ขึ้นอยู่กับการออกแบบฐานข้อมูลและคำสั่งที่ใช้ค้นหาด้วย
• หากต้องการข้อมูลเพิ่มเติม แนะนำให้ดูจากข้อมูลอ้างอิง

ข้อมูลอ้างอิง

• เพิ่มความเร็วการ SELECT ด้วย MySQL Query Cache
• MySQL Database Indexing for dummies