ในตอนที่แล้ว ผมได้อธิบายคำสั่งไปแล้ว แต่ก็เกิดข้อสงสัยอยู่ว่า วิธีไหนมีประสิทธิภาพมากกว่ากัน ซึ่งก็มีเพื่อนสมาชิก (คุณ Somsak Sae-Lim) ให้คำแนะนำวิธีที่สองมา รวมถึงออกแบบการทดสอบมาให้ด้วย

ชุดคำสั่งแรกเป็นการสร้างฐานข้อมูลพร้อมข้อมูลตัวอย่าง จำนวน 10000 รายการ และจำกัดข้อมูลให้อยู่ในช่วง 1-100 เพื่อให้ผลลัพท์ที่ได้ไม่มากเกินไป (ลดเวลาการส่งข้อมูลกลับ)

drop table if exists table_name;
create table table_name (id int not null primary key auto_increment, status int, repair_id int);
drop procedure if exists spGenData;
delimiter //
create procedure spGenData(maxRows int)
begin
declare i int;
set i = 1;
while (i < maxRows) do
insert table_name values(i, 1 + FLOOR(RAND()*100), 1 + FLOOR(RAND()*100));
set i = i + 1;
end while;
end;
//
delimiter ;
call spGenData(10000);
drop procedure spGenData;

หมายเหตุ คำสั่งด้านบนมีการดัดแปลงจากต้นฉบับเล็กน้อยนะครับ และการจำกัดข้อมูลรวมถึงผลลัพท์ก็เพื่อให้ไม่ต้องเสียเวลาในการคอยมากนัก

ทดสอบวิธีที่ 1 (SUBQUERY)

SELECT `id`,`status`,`repair_id`
FROM `table_name` AS S
WHERE S.`id`=(
  SELECT MAX(`id`)
  FROM `table_name`
  WHERE `repair_id`=S.`repair_id`
)

ผลลัพท์ ใช้เวลาทั้งหมด 16,228ms

ทดสอบวิธีที่ 2 (JOIN)

SELECT S.`id`,S.`status`,S.`repair_id`
FROM `table_name` AS S
JOIN (
  SELECT `repair_id`, max(`id`) AS `max_id`
  FROM `table_name`
  GROUP BY `repair_id`
) AS T ON T.`repair_id`=S.`repair_id` AND S.`id`=T.`max_id`

ผลลัพท์ ใช้เวลาทั้งหมด 9ms

จากผลลัพท์คงไม่ต้องบอกนะครับ ทิ้งกันแบบไม่เห็นฝุ่นทีเดียว

เหตุผลที่ในแบบแรก (SUBQUERY) ค่อนข้างช้าเนื่องมาจาก การประมวลผลในแต่ละแถวของตาราง table_name (Outer Query) จะไปทำการเรียก SUBQUERY ทุกครั้ง ทำให้การประมวลผลรวมจะมากถึง rows*rows หรือ 10000*10000 แถวเลยทีเดียว ส่วนในแบบที่สอง (JOIN) จะมีการประมวลผล SUBQUERY เพียงครั้งเดียวเท่านั้น เนื่องจาก จากการ GROUP BY จะให้ผลลัพท์ที่แต่ละ repair_id ออกมารอไว้แล้ว ทำให้ไม่ต้องไป Query ซ้ำอีก ซึ่งจะทำให้การประมวลผลมีเพียง rows * ผลลัพท์จาก SUBQUERY + 1 หรือมากสุดก็ไม่เกิน rows * 2 +1

นอกจากนี้เรายังสามารถเพิ่มประสิทธิภาพของ QUERY ด้วยการกำหนด Index ให้กับ repair_id ได้อีกด้วย

CREATE INDEX `repair_id` ON `table_name` (`repair_id`);

ผลการทดสอบวิธีที่ 1 (SUBQUERY) หลังจากเพิ่ม Index แล้ว
ผลลัพท์ ใช้เวลาทั้งหมด 1,884ms

และผลการทดสอบวิธีที่ 2 (JOIN)
ผลลัพท์ ใช้เวลาทั้งหมด 1ms