Add index to table in MySQL for faster query

• ประเด็นไม่มีไรมาก คือได้ไปมีส่วนร่วม เพิ่ม page ให้ระบบงานระบบหนึ่ง
• ซึ่งระบบนั้นใช้ PHP + Apache2 + MySQL + CentOS
• page ที่เราทำเพิ่มก็ไม่มีไรมาก query ข้อมูลจาก table ออกมาแสดง
• ซึ่งมีอยู่ 3 table ที่ต้อง join กัน งานแค่เนี๋ย ทำไม่กี่ชั่วโมงก็เสร็จ
• สำหรับคนไม่มีพื้น PHP อย่างตัวคนเขียนเนี๋ย
• จะทำอาไรสักคำสั่งก็ต้อง google เพื่อหาการใช้งาน function นั้นๆ ว่าต้องทำอย่างไร
• จึงค่อนข้างเสียเวลาหว่า เล็กน้อยถึงค่อนข้างมาก เลยปาเข้าไป เป็นชั่วโมง
• แต่สำหรับท่านที่พอมี พื้น PHP บ้างน่าจะทำไม่เกิน 10 นาที
• หลังจากนั่งงมโค้ด PHP + MySQL อยู่ตั้นนาน ก็เสร็จ
• ปัญหาต่อมาหลังจาก page นั้นใช้งานได้แล้ว แต่...มันช้ามากๆ
• คือ ข้อมูลแค่ 5 พันกว่า record ทำการ join แค่ 3 table แต่ใช้เวลา query หลายนาทีเลยอ่ะ
• หาข้อมูลใน google ต่อว่าจะทำอย่างไรให้ query ได้เร็ว
• ตอนแรกคิดว่า ลองสร้าง view ดูดีมั้ยหว่า แต่คิดว่ามันคงไม่เร็วขึ้นหรอกหว่า (คิดเอาเอง ยังไม่ได้ลอง)
• และความพยายามก็เป็นผล คือ การสร้าง index ให้ column ใน table สามารถทำให้ query ได้เร็วหว่า
• หรือ การทำ cache ให้ MySQL ก็สามารถเพิ่มความเร็วในการ query ได้เช่นกัน

เพิ่มความเร็ว query ด้วยการ สร้าง index ใน MySQL

Query ด้วยคำสั่ง

select a.chat
, a.pid
, a.title
, c.title_n
, a.fname
, a.lname
, a.birthdate
, a.sex
, b.hospsub_n
, b.mooban_n
from all_null_update a
, mastercupid b
, titlename c
where a.chat = b.mastercup_id
and a.title = c.title_c

Befor

• จำนวน 5711 record เชื่อม 3 table

• ใช้เวลาไป 6 นาที 17 วินาที เลยอ่ะ คนเปิดเว็บใครจะรอไหวเค้าคงนึกว่ามันค้างมากกว่าน้อ
• อันนี้ขนาด query บนเครื่องตัวเองยัง ช้าขนาดนี้ แล้ว connect ไปที่ server จะช้าขนาดใหน

After

• ทดสอบสร้าง index ให้แต่ละ column เราที่เราใช้เป็นเงื่อนไขบ่อยหลัง where หรือ ใช้ใน join table อ่ะ
• คำสั่งสร้าง index ของตัวอย่างนี้เป็นดังนี้

ALTER TABLE a ADD KEY (chat);
ALTER TABLE a ADD KEY (title);
ALTER TABLE b ADD KEY (mastercup_id);
ALTER TABLE c ADD KEY (title_c);

• หลังจากเราได้เพิ่ม index เข้าไปแหละ เราใช้เวลา query แค่ 318 มิลลิวินาทีเองหว่า
  
• ไม่ถึงนาทีด้วยซ้ำ เร็วขึ้นโค้ดๆ

ผลกระทบเมื่อทำ index

• การ insert, update ทำได้ช้าเพระต้องไปสร้าง index ให้กับ column นั้นๆ ของ record ใหม่ด้วย
• พื้นที่บน database ก้อนนั้นๆ มีการใช้มากขึ้น ง่ายๆ ก็คือ มีการใช้พื้นที่เพิ่มขึ้นในการเก็บสร้าง index
  

เพิ่มเติม

• index อาจมีข้อจำกัดอยู่บ้าง เมื่อ query นั้นมี in หรือ like มาเกี่ยวข้อง เค้าว่ามา
• เราสามารถลบ index ของ column ออกด้วยคำสั่ง

ALTER TABLE items DROP KEY item_descr;

• เพิ่ม index จะมี ( ) ครอบชื่อ column ที่ต้องการทำ index
• ส่วน การลบ สังเกตุว่าจะไม่มี ( ) ครอบชื่อ column ที่ต้องการถอน index ออกนะครับพี่น้อง
• จำได้ว่า SQL Server เมื่อเราทำการสร้าง table column ที่เรากำหนดเป็น PK จะมีการสร้าง index ให้เลย ไม่จำเป็นต้องสร้างเองสำหรับ PK ใน table นั้นๆ
  
• แต่ MySQL ไม่สร้างให้ หรืออย่างไรงง
  

ปล.

• ทดสอบสร้าง index แค่ table เดียว
  

ALTER TABLE b ADD KEY (mastercup_id);

• เวลาในการ query ใช้ได้เลยหว่า นึกว่าต้องสร้าง index กับทุก table ที่ join ซะอีกนะเนี๋ย
• แต่มีปัญหากับ การแสดง row number dynamic ซะงั้น งง ต้องเพิ่มงี้ด้วยถึงหายหว่า

ALTER TABLE c ADD KEY (title_c);

• สรุปง่ายก็ add index ให้ table ที่เราทำการ join ด้วยนั่นแหละ ไม่ต้อง add ให้ table หลักก็ได้
• ในที่นี้ table หลักคือ a เอาไป join กับ b และ c เราก็ add index ให้ FK ของ b และ c ก็พอ
• ถ้าผลลัพธ์เรามี rank dynamic ด้วยมันจะ เรียงลำดับให้ถูกต้องด้วยแหละ
• แต่ถ้าเราไม่ join กับ table อื่นๆ เราก็เพิ่ม index ให้กับ column ที่เราใช้ตามหลัง where บ่อยๆ
  

อ้างอิง

• http://spalinux.com/2008/06/indexing_mysql_database
• http://spalinux.com/2009/04/speed_up_select_with_mysql_query_cache
• http://www.thaisapclub.com/forums/showthread.php?t=128
• http://www.narisa.com/forums/index.php?showtopic=11909
• http://www.narisa.com/forums/index.php?showtopic=11830
  
• http://stackoverflow.com/questions/127156/how-do-i-check-if-index-exists-on-a-table-field-in-mysql
• http://www.tizag.com/mysqlTutorial/mysql-index.php
• http://www.narisa.com/forums/index.php?showtopic=14734
• http://www.narisa.com/forums/index.php?showtopic=5778