บันทึกผลของการเลือกใช้ SELECT แบบต่าง ๆ
เดเบียน Etch, Postgresql 8.1

มีตารางสองตาราง

1. ตาราง ft_word เก็บคำศัพท์ประมาณ 20,000 เรคคอร์ด
   • wordid = ไอดีคำศัพท์
   • word = คำศัพท์
2. ตาราง ft เก็บการกระจายศัพท์ มีประมาณ 6 แสนเรคคอร์ด
   • wordid = ไอดีคำศัพท์
   • mftid = ไอดีที่จะชี้ไปที่ตารางเก็บ
   • occur = จำนวนคำศัพท์ที่พบในตารางเก็บ

การค้นหาเพียงศัพท์เดียว ลักษณะจะเหมือนกัน คำสั่งคือ

SELECT mftid AS mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%various%')

จับเวลาได้ 35ms

แบบแรก ใช้คำสั่ง INTERSECT
สองคำศัพท์

(SELECT mftid AS mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%various%'))
INTERSECT
(SELECT mftid AS mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%types%'))

จับเวลาได้ 400ms
สามคำศัพท์ ได้ 440ms
สี่คำศัพท์ ได้ 450ms
ห้าคำศัพท์ ได้ 500ms
หกคำศัพท์ ได้ 520ms ด้วยคำสั่ง

(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%various%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%types%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%debugging%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%builds%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%enable%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%available%'))

เวลาที่ใช้ในการค้นหาคำศัพท์ เพิ่มขึ้นมากในครั้งแรก และเพิ่มขึ้นเล็กน้อยในครั้งต่อ ๆ มา

แบบที่สอง ใช้คำสั่ง SELECT ต่อ ๆ กัน
สองคำศัพท์

SELECT DISTINCT a1.mftid FROM 
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%various%')) AS a1,
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%types%')) AS a2
WHERE a1.mftid=a2.mftid

จับเวลาได้ 100ms
สามคำศัพท์ ได้ 80ms
สี่คำศัพท์ ได้ 150ms
ห้าคำศัพท์ ได้ 300ms
หกคำศัพท์ ได้ 450ms ด้วยคำสั่ง

SELECT DISTINCT a1.mftid FROM 
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%various%')) AS a1,
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%types%')) AS a2,
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%debugging%')) AS a3,
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%builds%')) AS a4,
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%enable%')) AS a5,
  (SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE '%available%')) AS a6
WHERE a1.mftid=a2.mftid AND a2.mftid=a3.mftid AND a3.mftid=a4.mftid AND a4.mftid=a5.mftid AND a5.mftid=a6.mftid

เวลาในการค้นหา เพิ่มขึ้นเกือบเท่าตัวในทุก ๆ คำศัพท์ที่เพิ่มขึ้นไป

UPDATE
ทดลองไป ๆ มา ๆ พบว่าความช้าไปตกอยู่ที่การค้นหาศัพท์
ไม่ได้อยู่ที่คำสั่ง INTERSECT
ถ้ามี wildcard "%" อยู่หน้าศัพท์จะทำให้ผลการค้นหาช้าลงมาก
ซึ่งในการใช้งานจริง การค้นหามักจะเอา wildcard อยู่ข้างหลังอยู่แล้ว
จึงปรับคำสั่งในวิธีแรกเป็น

(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'various%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'types%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'debugging%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'builds%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'enable%'))
INTERSECT
(SELECT mftid FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'available%'))

ได้ผลออกมาที่ 145 ms

แต่การใช้งานจริงจะแก้คำสั่ง INTERSECT เป็น UNION แทน
แล้วเติมฟิลด์น้ำหนักให้กับคำสั่ง
คำสั่งสุทธิจะกลายเป็น

SELECT a.weight, c.parent, c.field, b.parentid, a.occur FROM (
  SELECT DISTINCT SUM(weight) AS weight, mftid, SUM(occur) AS occur FROM 
    (
    SELECT 30 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word='various')
    UNION
    SELECT 30 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word='types')
    UNION
    SELECT 30 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word='debugging')
    UNION
    SELECT 30 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word='builds')
    UNION
    SELECT 30 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word='enable')
    UNION
    SELECT 30 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word='available')
    UNION
    SELECT 10 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'various%')
    UNION
    SELECT 10 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'types%')
    UNION
    SELECT 10 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'debugging%')
    UNION
    SELECT 10 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'builds%')
    UNION
    SELECT 10 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'enable%')
    UNION
    SELECT 10 AS weight, mftid, occur FROM ft WHERE wordid IN (SELECT wordid FROM ft_word WHERE word LIKE 'available%')
    ) 
  AS a GROUP BY a.mftid ) 
AS a, ft_main AS b, ft_parent AS c
WHERE a.mftid=b.mftid AND b.pftid=c.pftid
ORDER BY weight DESC, occur DESC

ได้ผลอยู่ที่ 450 ms

ถ้าเติม LIMIT 10 OFFSET 0 ต่อท้ายคำสั่ง
เวลาค้นจะเหลือเพียง 200ms

หมายเหตุ
ตารางเพิ่มเติมจากตัวอย่างคือ

1. ตาราง ft_main ใช้เก็บ
   • mftid คือ ไอดีของตารางนี้
   • pftid คือ ไอดีของตารางที่เก็บข้อมูลของ parent
2. ตาราง ft_parent ใช้เก็บ
   • pftid ไอดีของตารางนี้
   • parent ชื่อตาราง parent
   • field ชื่อฟิลด์ของ parent