DominixZ : Live Smart & Geeky

ถ้าใครเคยใช้ Fulltext จะรู้ว่า Fulltext นั้นค้นหาข้อมูลเร็วมาก แต่ก็ต้องมาเจ็บปวดเมื่อต้องใช้ “ORDER BY” เมื่อ Record ยิ่งเยอะยิ่งช้ามากๆ เมื่อคืนผมเจอปัญหาก็คิดไม่ตก คิดไปคิดมาไปอ่านการทำงานของ Order By Optimization ซึ่งอ่านไปแล้วก็ลองทำตามก็ไม่ได้ผล แม้จะอัพเรื่องจำนวน Memory ของ sort_buffer_size , read_rnd_buffer_size ก็ไม่หายตอนนั้นก็เกือบตี 4 แล้วถอดใจ กะว่าได้ใช้ Inverse Index แน่ๆแต่พอนอนลงแล้วนึกทบทวนเกี่ยวกับที่ได้อ่าน Order By Optimization จำประโยคได้ว่า

“Read all rows according to key or by table scanning. Rows that do not match the WHERE clause are skipped.”

คือถ้ามีการใช้ Where มันจะ Skip ข้อมูลตรงส่วนที่เหลือทิ้งไป งั้นเราก็มาดูตัวอย่างกันครับว่า Query ของผมเป็นยังไง

SELECT id,title,created_at,updated_at FROM pages WHERE root_page_id IN (1,2,3,4,5,6,7) AND MATCH (title,content) AGAINST (‘”ais” “dtac” “true”‘ IN BOOLEAN MODE) AND id NOT IN (1000,1001,1002,1003) ORDER BY created_at DESC LIMIT 0,20

โดยทั้งหมดนี้ผมทำ index แบบธรรมดาคือไม่ได้ composition ทั้งหมดแต่ก็มี index ทุก field ใน query นี้แต่ผลที่ได้ของเดิมก็คือ 20 sec ต่อมาก็เลยทดสอบว่าถ้า scope created_at ไว้น่าจะเร็วขึ้น ตามที่เขาเขียนข้างบนเลยกลายเป็น

SELECT id,title,created_at,updated_at FROM pages WHERE created_at >= ’2011-04-05 23:59:59′ AND created_at <= ’2011-05-05 23:59:59′ root_page_id IN (1,2,3,4,5,6,7) AND MATCH (title,content) AGAINST (‘”ais” “dtac” “true”‘ IN BOOLEAN MODE) AND id NOT IN (1000,1001,1002,1003) ORDER BY created_at DESC LIMIT 0,20

แล้วก็ลองทดสอบดูสรุปก็ยังเป็น 20 วินาทีอยู่ดีช้าอยู่ดี ก็ส่งสัยว่าทำไมๆๆ ก็เลยนึกถึงความเป็นจริงลองคิดว่า MySQL เป็นคน “created_at” ต่อให้มี index เราก็ต้องไปดู index ทั้งหมดอยู่ดีว่า id ไหนบ้างนะที่อยู่ใน range นี้โดยลองคิดดูว่ามีสัก > 500K เสร็จแล้วยังไม่วายโดน order_by อีกก็เลยยังช้าเหมือนเดิม แล้ววิธีคิดแบบไหนละที่จะทำให้เร็วขึ้น

ลองคิดดูว่าถ้าคุณมีตระกร้าของแต่ละคำที่ต้องการค้นหา (ในที่นี้ก็คือ fulltext และ index) เสร็จแล้วเอาตระกร้าเหล่านั้นออกมาก่อน แล้วค่อยมาเรียงมันจะเร็วกว่าไหม ? (เพราะแบบอันแรกเรายังไม่เทตระกร้าแล้วค่อยออกมาเรียง scope แต่เป็นการหา “ตะกร้าที่เกี่ยวข้อง” แล้ว “เรียงทั้งหมดใน table” เพราะยังไม่เท่ตะกร้าเนี้ยและ) คงส่งสัยใช่ไหมว่า MySQL มันจะโง่ยังงั้นเลยหรอ อันนี้ผมก็ไม่แน่ใจ แต่ผมใช้แบบ Query นี้

SELECT id,title,created_at,updated_at FROM pages WHERE id IN (SELECT id FROM pages WHERE root_page_id IN (1,2,3,4,5,6,7) AND MATCH (title,content) AGAINST (‘”ais” “dtac” “true”‘ IN BOOLEAN MODE) AND id NOT IN (1000,1001,1002,1003) ) ORDER BY created_at DESC LIMIT 0,20

เชื่อไหมว่า Query ผมจาก 20 วินาทีเหลือเพียง 0.3-1 วินาทีเท่านั้น !! นั้นหมายความว่าเร็วขึ้น 20x – 70x เท่าเลยทีเดียวนะครับ โดยไม่ต้องไปใช้ Inverse Index แต่ใช้วิธีนี้ แค่นี้ผมก็ยังไม่ต้องรีบเปลี่ยน Software Architecture และไม่ต้องไปเปลื้อง RAM จำนวนมากเพื่อให้ตอบโจทย์นี้เพราะมันจะต้องกิน RAM ประมาณ 6GB ในข้อมูลขนาด 650,000 rows ของผมซึ่งผมไม่มี ฝากบทความนี้กับคนที่กำลังจะหาวิธีใช้ Fulltext กับ Order By นะครับ

โดย Query เนี้ยผมทำกับระบบชื่อ OB-VOC ครับลองอ่านดูที่ Thumbsup ครับ

เนื่องจากผมจะต้องทำ Report พร้อมมีสมการบางอย่าง เพื่อเป็นการคำนวณ ซึ่งผมใช้ PHP กับ MySQL เป็นหลัก โดยปัญหาที่มักจะเกิดบ่อยๆก็คือ “ดึงข้อมูลจาก Database มาคำนวณที่ PHP แล้วก็เอามาเรียงใส่ Array ใหม่เป็นข้อมูลชุดใหม่” และยังมีเหตุการอื่นๆอีกเช่น ไม่ยอม Join Table แล้ว GET ข้อมูลจาก Table หนึ่งไปใส่อีก Table หนึ่งทำให้ Performance ช้าอีก

วันนี้ผมเลยขอแนะนำตัวที่เราได้ใช้บ่อยๆกันใน MySQL กันนะครับผมก็พึงลองสดๆร้อนๆเลย

CASE

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

จริงๆผมสับสนระหว่าง CASE กับ IF แต่พอใช้แล้วก็เข้าใจขึ้น โดยผมมองว่า CASE คือการ FILTER ข้อมูล แต่ IF เป็นเงื่อนไข แต่ปกติเราเขียนโปรแกรมมักใช้ IF เป็นการ FILTER ข้อมูล แต่ใน Database มักแยกกันชัดเจน ยกตัวอย่าง เช่น if(number == 1) ใน php ที่จริงคือ CASE WHEN number = 1 ทำนองนั้นครับ ตัวอย่างที่ชัดเจนคือ

SELECT page_id , SUM(CASE WHEN score = 1 THEN 1 ELSE 0 END) AS good , SUM(CASE WHEN score = 0 THEN 1 ELSE 0 END) AS neutral , SUM(CASE WHEN score = -1 THEN 1 ELSE 0 END) AS bad
FROM sentiments

โดยใน Table มี Field หนึ่งที่เก็บ score ซึ่งส่วนใหญ่แบ่งเป็น 1 , 0 , -1 ซึ่งผมต้องการ 3 เลขนี้ขึ้นมาใน Row เดียวผมเลยต้องใช้วิธีนี้ เพราะมันจะเกี่ยวโยงกับ Subquery เพื่อนำไปใช้ต่อในการคำนวณ ดังนั้นมันเลยเป็นสิ่งจำเป็นที่ต้องได้ค่าเหล่านี้มาอยู่ใน Row เดียว

IF

IF(expr1,expr2,expr3)
เป็นการใส่เงื่อนไขอย่างที่อธิบายก่อนหน้านี้ โดยวิธีใช้คือ  IF(SUM(score) <  80,IF(SUM(score) < 50,’F',’C'),’A') มันก็คือ IF ELSE ใน PHP นั้นเอง แต่สิ่งเดียวที่มันทำไม่ได้คือ IF(score == 1,’YES’,'NO’) ทำนองนี้ เน้นอีกครั้งนะครับ ทำไม่ได้ ดังนั้นเราเลยต้องใช้ CASE มาผสมกัน

อื่นๆ

อาจจะมีบางคนไม่รู้ว่า จริงๆแล้ว SQL สามารถ + – * / ( ) ได้ยกตัวอย่าง เช่น SELECT page_id , score/100 FROM sentiments เป็นต้น

รู้พื้นฐานแล้วแต่ก็คงยังไม่พอต้องผสมให้เป็น

โดยผมจะลองยกตัวอย่าง Query ซ้อน Query มาให้ดูสักอันจากของจริงนะครับ

SELECT page_id,create_date, IF(ABS((((good/devideval) + (neutral/devideval)) – ((bad/devideval) + (neutral/devideval)))) > 7,IF((((good/devideval) + (neutral/devideval)) – ((bad/devideval) + (neutral/devideval))) >0,1,-1),0) AS score
FROM
(
SELECT page_id , SUM(CASE WHEN score = 1 THEN 1 ELSE 0 END) AS good ,SUM(CASE WHEN score = 0 THEN 1 ELSE 0 END) AS neutral , SUM(CASE WHEN score = -1 THEN 1 ELSE 0 END) AS bad , COUNT(score) as total , (COUNT(score) / 100) as devideval , DATE(created_at) AS create_date
FROM `sentiments`
WHERE user_id = 1 GROUP BY page_id,DATE(created_at)
) AS sentiment_result

โดยแม้จะเป็น Query ที่ดูยากสักหน่อย แต่ผมจะอธิบาย SELECT ล่างก่อนนะครับโดยจะอ่านเป็นประมาณนี้ครับ

“ให้ทำการเลือก page_id , ผลรวมของ score โดย score มีค่าเป็น 1 ให้มีค่าเป็น 1 อย่างอื่นเป็น 0 , ผลรวมของ score โดย score มีค่าเป็น 0 ให้มีค่าเป็น 1 อย่างอื่นเป็น 0 เป็น good , ผลรวมของ score โดย score มีค่าเป็น 0 ให้มีค่าเป็น 1 อย่างอื่นเป็น 0 เป็น neutral , ผลรวมของ score โดย score มีค่าเป็น -1 ให้มีค่าเป็น 1 อย่างอื่นเป็น 0″

อธิบายเพิ่มเติมตรงส่วนนี้คือ การที่ต้องทำให้ต้องมีค่าเป็น 1 เป็น 0 นั้นเพราะความต้องการของผมคือ “จำนวนนับของ score 1 , 0 และ -1″ เลยต้องทำแบบนั้น จะ COUNT ก็ได้เช่นกัน แต่ผมขี้เกียจแก้ของผมที่ทำได้แล้วเท่านั้นเอง

ส่วนที่เหลือก็ Where ตามปกติ แต่ที่นี้ผมต้องอธิบายเพิ่มเพราะผมนำ SELECT อันล่างไปเป็นข้อมูลของ SELECT ข้างบนดังนั้นตอนนี้ผมเลยมี page_id , good , neutral , bas , total , devideval , create_date ที่สามารถเรียกใช้งานได้ ใน SELECt ด้านบนผมเลยมาทำสมการเลข สังเกตุว่าผมใส่ ( ) ค่อนข้างมาก เพื่อความมั่นใจว่า มันจะหารก่อนแล้วค่อยนำมา + – กันเสร็จแล้วผมก็ยังใช้ IF ELSE ฉบับ MySQL อีกด้วยคือ IF(express,IF(express,result1,result2),result3) ทำนองนั้นครับ

ประโยชน์ที่ได้รับสำหรับการใช้การคำนวณอยู่ใน Database

• เร็วไม่เปลื้อง  CPU , Memory ในการทำ Array ชุดใหญ่ใน PHP
• สำหรับผม Maintenance ทำได้สะดวกกว่าใช้ PHP SQL สลับไปสลับมา

โดยหลักๆคือ 2 ข้อแต่ประโยชน์จะทวีคูณ หากนำไปใช้กับ PHP อย่างเหมาะสม โดยคำเตือนขอเดียวก็คือ “ให้รู้ Limit ของ SQL และไม่จำเป็นต้องตั้งเงื่อนไขว่า จะได้ข้อมูลเหล่านี้ด้วย SQL ล้วนๆ มีบางกรณีของผมซึ่งใช้ Query เดิมได้แต่ต้องนำ good*1 , neutral*0 , bad*-1 แล้วนำมา + กันทั้งหมด ซึ่งตามจริงแล้วการจะได้เช่นนั้นผมจะต้อง SELECT อีกครั้งหนึ่งซึ่งมันสุดโต่งเกินไป ผมเลยหยุดที่ก่อน Step นั้นแล้วเวลานำไปใส่ใน Report ผมก็ใช้ PHP + – เอาแต่ทีนี้มันง่ายแล้วไงครับ เพราะใน Row ก็มี good , neutral , bad มาให้พร้อมแล้วแค่ใส่สมการ เอาไปแสดงผลเป็นอันเสร็จ วันนี้ผมลาไปก่อนหวังว่ามีประโยชน์ไม่น้อยสวัสดีครับ