Random Row เรื่องเล็กๆ ที่สามารถสร้างปัญหาใหญ่ๆ

หลังจากที่มีข้อมูลใน Database MySQL เยอะขึ้นแล้ว สิ่งที่มองข้ามในหลาย ๆ จุดก็เริ่มแสดงผลขึ้น

เรื่องง่ายอย่างเช่นการสุ่มแสดงผลโดยใช้ RAND() ใน Mysql กลับใช้ทรัพยากรอย่างมากในการสุ่มแต่ละครั้ง

ทำให้ Server เริ่มเกิดอาการโหลดหนัก เนื่องจากมีการสุ่มข้อมูลเพื่อนำไปใช้ตลอด

(กว่าจะเจอต้นตอของความช้าก็ไล่ Benchmark Code แต่ละส่วนนานอยู่)

 

สำหรับเว็บที่มีแถวข้อมูลเป็นจำนวนมาก การใช้ RAND() นั้นไม่เหมาะแน่ ข้างล่างคือ Code เจ้าปัญหา

[Codeigniter]

$query = $this->db->limit(1)->order_by(‘unique_id’, ‘random’)->get(‘product’);

ผลลัพธ์ที่ออกมาได้หลังจาก Benchmark คือ 0.0693 วินาที อาจดูไม่มาก

แต่ด้วยจำนวน Req กับการที่ MySQL มันซด CPU ไปเยอะ แทบจะโดน VPS Provider เตะออก

 

คราวนี้มาแก้ไขกัน

ทั่วไปเค้าจะให้นำตัวเลขที่เป็น ID [auto increasement] มาหาค่า MAX แล้วสร้าง Random ตัวเลขเปล่าขึ้นมา

หลังจากนั้นก็นำตัวเลขนั้นไป Query Where ตรง ๆ แต่ในกรณีของผม ตัวตาราง DB ของสคริปนั้นไม่ได้มี AI Number

จึงเปลี่ยนมาใช้การตั้ง Limit Offset แทน ตัวอย่างโค๊ด ด้านล่าง

$count = $this->db->count_all_results(‘product’);
$query = $this->db->limit(1, rand(1, $count))->get(‘product’);

ผลลัพธ์ที่ได้คือ 0.0079 วินาที ไวกว่าเกือบ 10 เท่า และไม่ต้องกังวลเมื่อมี Product เพิ่มมากขึ้น

หลังจากที่ได้นำวิธีนี้ไปลองใช้ สามารถลดการใช้ CPU ไปได้อีกเยอะ

Trim ผ่าน MySQL โดยตรง

UPDATE smf_messages set subject= TRIM(subject);

ตัวอย่าง :: trime หัวข้อเว็บบอร์ด smf

และ

UPDATE smf_messages set subject= replace(subject,’\t’,”);

สำหรับบางกรณีที่เป็น Tab Character

วิธี Group การค้นหา Where แบบ OR

ปกติถ้าต้องการค้นหาบางฟิลด์แบบหลายเงื่อนไข บางคนอาจใช้วิธี Loop Query ตั้งแต่ต้นแล้วมาเชื่อมด้วย OR ใหม่ เช่น

SELECT * FROM Users WHERE FirstName = ‘Mac’ AND LastName = ‘Smith’ OR FirstName = ‘John’ AND LastName = ‘Jones’);

แต่คราวนี้เราจะใช้วิธี Group Where ที่ต้องการค้นหาแบบหลายผลลัพท์ ตัวอย่างด้านล่าง

SELECT * FROM Users WHERE FirstName = ‘Mac’ AND (LastName = ‘Smith’ OR LastName = ‘Jones’);

เท่านี้ MySQL ก็จะค้นหา FirstName = Mac โดยที่ LastName จะเป็น Smith หรือ Jones ก็ได้

การ Group By วันที่ ใน Format ต่าง ๆ

จะขอยกตัวอย่างใน codeigniter นะครับ ซึ่งถ้าใช้ Query String ก็ใช้แบบเดียวกัน

การ Group By เวลาของ Unix time

(Unix time จะเป็นตัวเลข 10 หลัก ซึ่งถ้าไม่ใช้ Function ของ MySQL ลำบากแน่ครับ)

$this->db->group_by(“FROM_UNIXTIME(ชื่อฟิลด์ที่ต้องการกรุ๊ป, ”%Y-%m-%d”)”);

จะเป็นการ Group ตามวันเรียง ๆ กันไป เราสามารถใช้อีกเงื่อนไข

เพื่อทำการนับได้ว่าตามวันนั้น ๆ ที่ List เรียงจาก MySQL มีจำนวนเท่าไหร่

$this->db->select(‘Count(“ชื่อฟิลด์ที่ต้องการกรุ๊ป“) as number,ชื่อฟิลด์ที่ต้องการกรุ๊ป ‘);

เช่น

2011-05-28 มี 5 แถว

2011-05-29 มี 3 แถว

ค่าฟิลด์ number ของวันที่ 2011-05-28 ตาม Unix time ก็จะเท่ากับ 5

ส่วน 2011-05-29 ก็จะเท่ากับ 3 ในเวลาที่เรา foreach ผลลัพท์ออกมาทีละแถว

คราวนี้มาดู วิธี Group Datetime ของ MySQL กันบ้าง

ถ้าเรามีฟิลด์ Datetime อยู่ แล้วอยากให้ Group กัน แค่วันที่เพื่อนับว่าวันนึงมีสถิติผู้ใช้เท่าไหร่ ให้ใช้คำสั่งนี้

$this->db->group_by(“DATE(ชื่อฟิลด์ที่ต้องการกรุ๊ป)”);

หลังจากนั้นถ้าต้องการนับผลลัพท์จำนวนแถวทั้งหมด (จำนวนแถวจริง ไม่ใช่แนวหลัง Group นะครับ) ก็ให้ใช้คำสั่งแบบเดิม

คือการ Count ชื่อฟิลด์ที่กรุ๊ปแบบเดียวกับด้านบน เท่านี้ก็เขียนวิธีนับแถวตามวันได้แบบง่าย ๆ แล้ว

 

ปล. สำหรับ Codeigniter ถ้ามีปัญหาให้ลอง ใช้คำสั่งนี้วาง บนและล่างของบรรทัดที่ใช้ MySQL Function เช่น

$this->_protect_identifiers = FALSE;

$this->db->group_by(“DATE(regis_datetime)”);

$this->db->select(‘Count(“regis_datetime”) as number,DATE(regis_datetime) as day’);

$this->_protect_identifiers = TRUE;