Programmer's Blog

[Startup CTO ตอนที่ 9 ] Full Text Search คืออัลไลย์ และปัญหาที่เจอกับ SQLite

พอดีวันก่อนมีโจทย์คือต้องทำ Full Text Search กับ Database บน SQLite เนื่องจากจะต้องเขียน app บน Android และ iOS เพื่อทำการค้นหาโดยชื่อจาก List ของ Contact ราวๆ 5000 กว่า Contact เลยเอาเรื่องนี้มาแชร์เป็นความรู้คร่าวๆ กันครับ เดี๋ยวจะหาว่ามีแต่เรื่องอื่นๆที่ไม่ใช่ Technical เลยเปลี่ยน Style กันบ้าง

เริ่มจากเกริ่นเรื่องของการ Search ใน MySQL สมมุติเราจะ Search ชื่อของ contact ที่มีตัวหนังสือ da โดยการ match แบบ contain (คือชื่อมี da เป็นส่วนหนึ่งของชื่อนั้นๆ )

ปกติการค้นหาทั่วใปใน SQL เราก็จะใช้

Select * from contact where firstname like ‘%da%’

โดยที่ผมเห็น programmer บางคนมักจะไม่ทำกัน ซึ่งจะทำให้การค้นนั้นช้ามา คือการ set field ที่เราจะต้องทำการค้นหาให้เป็น index ครับ อ่านเรื่องการทำ index ได้ที่ link นี้

ตัวอย่าง sql command คือ

ALTER TABLE `table` ADD INDEX `firstname` (`firstname`)

โดยการทำ index ก็จะทำให้ performance เพ่ิมมาระดับนึง แต่ถ้า database มี Row เยอะมากๆ เราจะต้องทำ Full Text Search ละ ซึ่งทั้งการ Create Table และการ Select จะใช้ command ที่ต่างกัน เช่น ตอนสร้าง Table

CREATE TABLE contact (
 id     INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 firstname  VARCHAR(200),
 note TEXT,
 FULLTEXT (firstname,note)
 );

ตัวอย่างนี้คือการบอกให้ mysql ทำ FULLTEXT ที่ field firstname และ note โดยเวลาค้นหา เราก็จะเปลี่ยน SQL Select ให้ใช้เป็น match แบบนี้

SELECT *
 FROM contact
 WHERE MATCH (firstname) AGAINST ('da');

แต่ก็ยังมี condition จำกัดบางประการของ Mysql อยู่นะครับ เช่นใน version mysql 5.6 ยังไม่รองรับภาษาไทย เพราะตัดคำกันยากอยู่ ลองอ่านเพิ่มเติมที่นี่ครับ 

ปัญหากับ sqlite

ที่นี้เรามาดูตัว sqlite กันบ้าง เนื่องจากใช้เป็น database สำหรับบน Android และ iOS โดย sql จะรองรับ Full Text Search อยู่แล้ว ด้วย extension FTS3 และ FTS4 ล่าสุดมี FTS5 แต่ ยังไม่ได้ลอง ลองอ่านกันเองนะครับ

ตัวอย่างการใช้ก็เช่น

CREATE VIRTUAL TABLE contact USING FTS4(content firstname);

โดยวิธีการ select โดย match suffix คือ ใช้ * ดังตัวอย่างข้างล่าง

SELECT * FROM contact WHERE firstname MATCH 'da*';

เห็นปัญหากันแล้วใช่ไม๊ครับ ไอ้ SQLite มันดันไม่มี *da* อ่ะดิ มันมีแต่ da* ดังนั้นชื่อคำว่า Krissada ไม่มากับ query นี้แน่นอน สิ่งที่จะมากับ query นี้ก็เช่น “dammit” “dating” “dasilva”

เลยมีคนคิดวิธีการแก้ไขโดยการ substring แล้วเพิ่มข้อมูลเข้าไปซะเลย เช่น ในตัวอย่างเราก็เพิ่ม field suffixes เอามาเก็บ suffixs ทั้งหมด เป็น

INSERT INTO contact(suffixes) VALUES("Krissada rissada issada ssada sada ada da a")

ที่นี้พอเรา search ด้วยคำสั่งเดิม SELECT * FROM contact WHERE firstname MATCH ‘da*’ ก็จะได้ Row ที่มีชื่อ Krissada มาละ

สนใจอ่านเพิ่มเติมจากที่มาได้ที่ Link นี้

ใครสงสัย comment ทิ้งไว้ได้

ติดตาม Blog ผมกันด้วยนะครัช

คุณอาจสนใจบทความนี้