วันนี้มีงานที่ต้องใช้การสุ่ม record มาแสดงผลจากแต่ก่อนที่เคยใช้ได้แต่วันนี้ใช้งานช้าลงมาก จากที่เคยเขียน การสุ่ม record ใน MySQL ซึ่งเป็นการใช้งานแบบง่ายๆ แต่เมื่อต้องใช้งานกับข้อมูลที่มีจำนวนมากๆ แล้วจะมีประสิทธิภาพด้อยลง เนื่องจากเราไม่สามารถใช้ประโยชน์ใดๆ จาก Index ต่างๆ ที่สร้างไว้ได้เลย

วันนี้เรามาดูการ optimization การสุ่ม record ให้มีประสิทธิภาพกันโดยเฉพาะให้มีการใช้งาน Index ได้อย่างเหมาะสม โดยตารางข้อมูลที่ทำอยู่มีข้อมูลประมาณ 30k records โดยยังคง Index ต่างๆไว้เหมือนเดิมโดยมี houseid เป็น Primary Key เป้าหมายต้องการสุ่ม record เหล่านั้นขึ้นมาแสดงผลจำนวน 4 records เรามาดูการใช้การสุ่มแบบง่ายกัน

SELECT houseid FROM mod_houses ORDER BY RAND() LIMIT 4;

จากการใช้คำสั่งนี้ใช้เวลา 6.84 วินาทีและ 3.68 วินาทีหลังจากการ warm แล้ว เราเริ่มจากการสุ่มจาก record ทั้งหมด

SELECT CEIL(RAND() * MAX(houseid)) FROM mod_houses;

คำสั่งนี้ใช้เวลา 0.01 วินาทีและ 0.0026 วินาทีหลังจากการ warm
จากนั้นเอามาใช้งานด้วย IN

SELECT houseid FROM mod_houses WHERE IN (SELECT CEIL(RAND() * MAX(houseid)) FROM mod_houses) LIMIT 4;

แต่วิธีนี้ไม่ดีแน่เนื่องจาก subquery จะถูกเรียกใช้งานเมื่อ SELECT ด้านนอกทำการอ่านข้อมูลทำให้เกิดการ query จำนวนมหาศาล เราต้องเปลี่ยนมาเป็นการ JOIN

SELECT h1.houseid FROM mod_houses h1 JOIN (SELECT (RAND() * (SELECT MAX(houseid) FROM mod_houses)) AS houseid) AS h2 ON h1.houseid>=h2.houseid ORDER BY h1.houseid ASC LIMIT 4;

คำสั่งนี้ทำให้เราได้ข้อมูลที่ต้องการออกมาโดยใช้เวลาเพียง 0.0012 วินาทีและ 0.0004 วินาทีหลังจากการ warm ที่เราต้องใช้ >= เนื่องจากบางครั้ง houseid อาจจะไม่มีจำนวนที่ตรงกับการสุ่ม ก็ให้ไปใช้ houseid ลำดับถัดไปแทน

จากการ optimize ครั้งนี้จะเห็นว่าความเร็วของการสุ่มเร็วขึ้นสูงถึง 9,200 เท่าซึ่งจะช่วยให้ application โดยรวมทำงานได้เร็วขึ้นมาก โดยเฉพาะในระบบที่มีจำนวน records มากๆและมีการเรียกใช้งานเยอะๆ

Optimize การสุ่ม record ใน MySQL