sql: บันทึก Benchmark

บันทึกผลของการเลือกใช้ 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
Topic: 
sql
benchmark
postgres

sql: บันทึกการแปลงตาราง

ความรู้ sql น้อยมาก ขออนุญาตบันทึกเอาไว้ดูครับ

ต้องการแปลงโครงสร้างตารางคือ
สมมุติว่ามีตาราง phone มีโครงสร้างดังนี้

CREATE TABLE phone 
(   phoneid	SERIAL,
    custid      VARCHAR(10),
    name        VARCHAR(128),
    phone       VARCHAR(64),
    cat         VARCHAR(64),
    rem         TEXT
);

เมื่อใส่ข้อมูลแล้ว ต้องการเปลี่ยนสดมถ์ custid เป็นข้อมูลชนิด integer
พบว่าไม่สามารถเปลี่ยนตรง ๆ โดยใช้คำสั่ง ALTER ได้

แปลงโดยใช้ตาราง temp เป็นตัวทด
ใช้เงื่อนไข CASE บวกฟังก์ชั่น CAST ในการแปลง

CREATE TABLE temp AS 
SELECT phoneid, 
  CASE WHEN custid='' THEN 0 ELSE CAST(custid AS INT) END AS custid,
  name,
  phone,
  cat
FROM phone;

แล้วจึงเอาตารางใหม่ไปทับตารางเก่า

DROP TABLE phone;
SELECT * INTO phone FROM temp;
DROP TABLE temp;

หรือ

DROP TABLE phone;
ALTER TABLE temp RENAME TO phone;

ทดลองกับเดเบียน etch, postgresql-8.1, phppgadmin-4.0.1

Topic: 
sql

sql: บันทึกค้นข้อมูลแบบให้น้ำหนัก

ขออนุญาตบันทึกการทดลองเพื่อเอาไว้ดูครับ

สมมุติถ้ามีตาราง t_phone เอาไว้เก็บข้อมูลลูกค้า มีโครงสร้างคือ

CREATE TABLE t_phone 
(   phoneid	SERIAL,
    custid      VARCHAR(10),
    name        VARCHAR(128),
    phone       VARCHAR(64),
    cat         VARCHAR(64),
    rem         TEXT
);

ถ้าเราต้องการค้นข้อมูลจากฟิลด์ rem เป็นข้อมูลสองชุด เช่น
ค้นว่าในฟิลด์ rem ต้องมีข้อมูลว่า 'S1' และ/หรือ 'S2' ประกอบอยู่
เราอาจใช้เงื่อนไขคือ

SELECT name, phone, cat, rem FROM t_phone
WHERE rem LIKE "%S1%S2"
    OR rem LIKE "%S1%"
    OR rem LIKE "%S2%"

ข้อมูลที่ได้จะเป็นข้อมูลผสม โดยไม่สามารถเรียงลำดับความสำคัญของข้อมูลได้

ในที่นี้ความต้องการคือ ต้องการให้เรียงลำดับความสำคัญว่า ถ้าพบข้อมูล "S1" และ "S2" อยู่ด้วยกัน จะมีความสำคัญมากกว่า แต่ถ้าพบเพียงข้อมูลตัวเดียว จะให้มีความสำคัญน้อยกว่า

ในที่นี้จึงต้องใช้คำสั่ง CASE เข้ามาช่วย โดยเพิ่มฟิลด์ score เขามาในการจัดเรียง
คำสั่งจะกลายเป็น

SELECT
CASE WHEN rem LIKE '%S1%S2%' THEN 4
  WHEN rem LIKE '%S1%' THEN 2
  WHEN rem LIKE '%S2%' THEN 2
  ELSE 0
END AS score,
name, phone, cat, rem FROM t_phone
WHERE rem LIKE "%S1%"
    OR rem LIKE "%S2%"
ORDER BY score DESC

เราจะได้ผลลัพธ์ที่ถูกเรียงลำดับตามความสำคัญเรียบร้อย

อีกวิธีนึงคือใช้ UNION ALL

SELECT SUM(score) AS score, name, phone, rem FROM
(
  SELECT 1 AS score, name, phone, rem FROM t_phone 
  WHERE rem LIKE '%S1%' 
UNION ALL
  SELECT 1 AS score, name, phone, rem FROM t_phone
  WHERE rem LIKE '%S2%' 
) AS a
GROUP BY a.name, a.phone, a.rem
ORDER BY score DESC

วิธีหลังดูจะช้ากว่า แต่ก็น่าจะเขียนโค๊ดง่ายกว่า

Topic: 
sql