ตอบแบบคนยังเรียน DB ไม่จบนะครับ ถ้ารันทุกคน คนละรอบ ข้อ 3 น่าจะ performance แย่สุด เพราะดึงข้อมูลทั้งหมดออกมาเตรียมไว้ก่อนแล้วค่อยคัด ข้อ 2 อาจจะดีขึ้นมานิดหนึ่ง แต่ก็ยังแย่อยู่กว่าข้อ 1 เพราะข้อ 2 ใช้วิธี cross แล้วค่อยคัด แต่อัน 1 เป็น cross คัด 2 รอบ (มั้งครับ)

ว่าแต่พอจะบอกได้ไหมครับว่าเอาไปทำอะไร ? จะได้เห็นนำสภาพแวดล้อมโดยรอบมาคิดด้วยว่าแบบไหนดีหน่ะครับ

ลองใช explain ใน phpmyadmin ดูสิครับ จะเห็นลำดับการทำงานมัน

ปล. อย่าลืมใส่ index ในตัวที่ใช้หาด้วยนะ

ข้อ 1 กับ 2 มัน query ไม่เหมือนกันนะครับ ข้อ 1 มันคือ left outer join ส่วนข้อ 2 มันก็คือ inner join เฉยๆ

สมมติว่าไม่มีข้อมูลขาด, ทำ index ไว้เรียบร้อย และ database ฉลาดพอ ข้อ 1 กับข้อ 2 จะได้ performance เท่ากันครับ เพราะ database มันจะรู้เองว่าการ where นั้นคือการ inner join นั่นเอง แต่ถ้าเจอ database ที่ไม่ฉลาด ข้อ 1 จะเร็วกว่า เพราะในข้อ 2 database มันจะ join ทั้งหมดมากรองอีกรอบนึง

ส่วนข้อ 3 อ่านแล้วไม่เข้าใจว่าคืออะไร เดาว่าคือดึงข้อมูลมาไว้ใน memory เลย ซึ่งถ้ามี memory พอ เวลารู้รหัส province, zone, district ก็ดึงจาก memory ได้เลย ไม่ต้องไป join ใน database จะเร็วสุดครับ

อัน 2 ดีสุดครับ
ผมไม่เห็นด้วยน่ะ กับความเห็นที่ว่า left join ใช้ performance น้อยกว่า ลองดูใน ref ของผมครับ เขาบอกไว้อยู่ว่าทำไม left join ไม่ได้ดีกว่า

ปัญหาที่ผมจะถามคุณ คือ ข้อมูลคุณมัน คงที่หรือเปล่า หรือมีการ update ตลอดเวลา เพราะถ้าคงที่ ผมแนะนำให้ใช้ materialized view ครับ

ที่มาของผม
http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server
http://msdn.microsoft.com/en-us/magazine/cc301622.aspx

ข้อ 3 ไม่ขอวิเคราะห์ เพราะดูเหมือนเป็น code โปรแกรมมาประกอบ ไม่ใช่ SQL แท้ๆ

ถ้าเป็น MS SQL Server ก็เปิดดู Execution plan แล้วสั่งทำงานทั้งคู่ แล้วดูว่า อันไหนเกิน % ของเวลาน้อยกว่า ก็อันนั้นเร็วกว่า

ข้อ 1 left outer join ซึ่ง table ที่อยู่หน้า LEFT OUTER JOIN จะโดนอ่านทุก rows ( อาจยกเว้นถ้ามีเงื่อนไขหลัง WHERE ) แม้จะมี index ทำให้อาจช้าอยู่ดี ที่มันอ่านทุก rows เพื่อเอา columns ของ table ที่อยู่หน้า LEFT OUTER JOIN มาแสดง ถ้า join ไม่ติด columns ของ table ที่อยู่หลัง LEFT OUTER JOIN จะได้ค่า null แทน และ table หลัง LEFT OUTER JOIN ก็อาจต้องโดนไล่อ่านทุก rows ทำให้ index ไม่ได้ใช้ประโยชน์อีก

ข้อ 2 เหมือนจะเป็น inner join แบบแอบๆ ไปใช้ WHERE แทน ON ตามความเห็นของเรา ถ้า province.province_id หรือ province.zone_id มี index ก็น่าจะเร็วขึ้น แต่อาจต้องดู selectivity ด้วยว่า index สร้างมา จะได้ถูกใช้ประโยชน์หรือไม่

select 1.00 * count(distinct province_id) /count( * ) from province

select 1.00 * count(distinct province_id) /count( * ) from district

select 1.00 * count(distinct zone_id) /count( * ) from province

select 1.00 * count(distinct zone_id) /count( * ) from zone

เช่นสมมติว่า table province มี column province_id เป็น primary key ซึ่งเท่ากับว่าค่าของทุก rows ใน column นี้จะไม่ซ้ำกันเลย แล้วถ้ามี index บน column นี้ มันจะถูกใช้อย่างคุ้มค่า เพราะเจอข้อมูล row เดียวแล้วไม่ต้องค้นต่อ ( ในความเป็นจริง ถ้าเป็น primary key คงจะมี index อยู่แล้ว )

แต่ถ้าสมมติว่า district มีเป็นล้าน rows แต่มี province_id ที่แตกต่างกันไม่เกิน 100 แบบ เท่ากับว่า ถ้าค้นเจอ จะหยุดค้นไม่ได้ เพราะอาจมีอีก ( ตัวอย่างคลาสสิกของกรณีนี้คือ column เก็บเพศชายหญิง ซึ่งเป็นไปได้ว่า ครึ่งหนึ่งเป็นชายครึ่งหนึ่งเป็นหญิง ) ถ้าต้องอ่าน rows เยอะๆ database server มันอาจไม่ใช้ index เพราะไม่คุ้มถ้าไหนๆ จะอ่าน table เยอะๆ

ส่วน columns ที่เป็นผลลัพธ์ ( columns ที่อยู่หน้า FROM ) ถ้าสามารถทำ index with include columns ก็น่าจะทำให้เร็วขึ้นได้ เดาว่า tables zone และ district คงมี index ขอเสนอ ข้อ 2 น่าจะดีกว่าข้ออื่นๆ

ล้านข้อมูล มี UIP สัก 1 แสน หมายความว่า เป็นเว็บซินะครับ

อย่างนี้นะครับ 1 กับ 2 ไม่มี where ดังนั้นมันหยิบข้อมูลมาหมด ก็คือช้าพอกัน
แต่ถ้ามี where ก็ขึ้นกับ index ถ้ามีเหมือนกัน ก็เร็วเหมือน ๆ กัน

ส่วน 3 ช้าสุดแน่นอน เพราะ query ครั้งเดียว กับ query หลาย ๆ ครั้ง ต่างกันมาก
โดยเฉพาะอันนี้ tab_province = select * from province where id = zone->id and id=district->id;

แบบแรกสำหรับตารางใหญ่ๆ จะเร็วขึ้นถ้าเลือกให้กรองครั้งแรกแล้วเหลือข้อมูลน้อย (เปลือง cpu ประหยัด ram)

แบบที่สองใช้กับการเข้าถึงตารางส่วนเล็กๆ ยิ่งเล็กยิ่งเร็ว (เปลือง ram ประหยัด cpu)

แบบสุดท้ายเอาไว้สำหรับข้อมูลที่ไม่ต้องการ update บ่อยๆ / update ที่อื่นโดยมีเวลาแน่นอน และ ดึงข้อมูลไปแล้วใช้หลายครั้ง เช่นราคาสินค้า update กลางคืน ดึงไปตอนเช้า ใช้ทั้งวันจนปิดร้าน (เร็วแบบมีข้อแม้)

เลือกใช้ให้เหมาะสม

ถ้าเป็น MSSQL และมีการ Index แบบต่างๆที่ควรจะเพิ่มได้

3 แย่สุด select * (ไม่สามารถใช้งาน Covering Index ได้)

1 และ 2 ดูแล้วระบบจะดึงข้อมูลทั้งหมดจาก TABLE ออกมาแล้วค่อยทำงานตามคำสั่งที่เขีียนไว้

แต่ภาษีของ 1 จะดีกว่าเพราะตัว engine ของ MSSQL ชอบแบบ 1 มากกว่า รวมทั้งการใส่ Hint หรือคำสั่งเพื่อเพิ่ม Performance แบบ 1 มันจะ่ง่ายกว่า