เพิ่มประสิทธิภาพการค้นหาข้อมูลใน MySQL ด้วย FULLTEXT SEARCH

หากต้องการค้นหาข้อมูลใน MySQL เราสามารถใช้ WHERE ระบุชื่อคอลัมน์ ตามด้วย LIKE หรือ RLIKE เพื่อค้นหาคำหรือรูปแบบคำที่เราต้องการค้นหา ถ้าฐานข้อมูลหรือตารางของเรามีขนาดไม่ใหญ่มากนัก วิธีนี้ก็สามารถใช้งานได้ แต่ถ้าฐานข้อมูลขนาดใหญ่ การใช้ LIKE หรือ RLIKE อาจทำงานช้า

ขอแนะนำการทำ FULLTEXT SEARCH ใน MySQL เพื่อเพิ่มประสิทธิภาพการค้นหา โดย MySQL จะมีการทำ index คำของคอลัมน์ที่ต้องการค้น คำนวณคะแนน แสดงผลลัพธ์ที่ได้สามารถเรียงตามเนื้อหาที่เกี่ยวข้องได้


ในที่นี้ทดลองกับ MySQL 5.1.61

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.1.61    |
+-----------+
1 row in set (0.00 sec)

เริ่มต้นสร้างตาราง (table) ชื่อ articles โดยระบุให้ทำ FULLTEXT SEARCH ชื่อคอลัมน์ title และ body

mysql> CREATE TABLE articles (
 id     INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 title  VARCHAR(200),
 body TEXT,
 FULLTEXT (title,body)
 );
Query OK, 0 rows affected (0.01 sec)

แม้ดีฟอลต์ ENGINE ของ MySQL จะเป็น InnoDB แต่ถ้ามีการระบุทำ FULLTEXT จะเป็นการสร้าง table ชนิด MyISAM

หากทดลองระบุ ENGINE ให้เป็นแบบ InnoDB

mysql> CREATE TABLE articles (
 id     INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 title  VARCHAR(200),
 body TEXT,
 FULLTEXT (title,body)
 ) ENGINE=InnoDB;
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

 

จะไม่สามารถสร้างได้ ขึ้นว่า ERROR 1214 (HY000): The used table type doesn’t support FULLTEXT indexes

MySQL เวอร์ชั่น 5.1, 5.5  การทำ FULLTEXT SEARCH ทำได้เฉพาะกับ table ชนิด MyISAM เท่านั้น

หมายเหตุ หากต้องการทำ FULLTEXT SEARCH กับ InnoDB ต้องใช้ MySQL เวอร์ชั่นใหม่ล่าสุด 5.6

ใช้คำสั่ง INSERT เพิ่มข้อมูลเข้าไปในตาราง

mysql> INSERT INTO articles (title,body) VALUES
 ('MySQL Tutorial','DBMS stands for DataBase ...'),
 ('How To Use MySQL Well','After you went through a ...'),
 ('Optimizing MySQL','In this tutorial we will show ...'),
 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
 ('MySQL vs. YourSQL','In the following database comparison ...'),
 ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
 Records: 6  Duplicates: 0  Warnings: 0

 

ทดลอง SELECT ข้อมูลออกมา

mysql> SELECT * FROM articles;
 +----+-----------------------+------------------------------------------+
 | id | title                 | body                                     |
 +----+-----------------------+------------------------------------------+
 |  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
 |  2 | How To Use MySQL Well | After you went through a ...             |
 |  3 | Optimizing MySQL      | In this tutorial we will show ...        |
 |  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
 |  5 | MySQL vs. YourSQL     | In the following database comparison ... |
 |  6 | MySQL Security        | When configured properly, MySQL ...      |
 +----+-----------------------+------------------------------------------+
 6 rows in set (0.00 sec)

 

วิธีค้นหา สามารถทำได้โดยใช้คำสั่ง WHERE MATCH AGAINST โดยระบุชื่อคอลัมน์ที่มีการทำ FULLTEXT แล้วระบุคำที่ต้องการหาในวงเล็บตามหลังคำว่า AGAINST

mysql> SELECT *
 FROM articles
 WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
 | id | title             | body                                     |
 +----+-------------------+------------------------------------------+
 |  5 | MySQL vs. YourSQL | In the following database comparison ... |
 |  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
 +----+-------------------+------------------------------------------+
 2 rows in set (0.00 sec)

 

ชื่อคอลัมน์ที่ระบุในวงเล็บตามหลัง MATCH ต้องตรงกับที่ระบุใน FULLTEXT ตอนสร้างตาราง

หากระบุ MATCH ไม่ตรง จะขึ้น error แบบนี้

mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('database');
 ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

 

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

หากต้องการดูผลคะแนนของผลลัพธ์การค้นหาด้วย ให้เพิ่ม MATCH AGAINST ในการ SELECT ด้วยเช่น

mysql> SELECT *, MATCH (title,body) AGAINST ('database') AS score
 FROM articles
 WHERE MATCH (title,body) AGAINST ('database')
 ORDER BY score;
+----+-------------------+------------------------------------------+-------------------+
 | id | title             | body                                     | score             |
 +----+-------------------+------------------------------------------+-------------------+
 |  1 | MySQL Tutorial    | DBMS stands for DataBase ...             | 0.655458331108093 |
 |  5 | MySQL vs. YourSQL | In the following database comparison ... | 0.662664592266083 |
 +----+-------------------+------------------------------------------+-------------------+
 2 rows in set (0.00 sec)

 

ลองนำไปใช้กันดูครับ

แต่การทำ FULLTEXT SEARCH ของ MySQL แม้กระทั่งเวอร์ชั่นใหม่ 5.6 ยังไม่สามารถค้นหาภาษาไทยได้ เนื่องจากปัญหาเรื่องการตัดคำ เท่าที่ลองหาข้อมูล ต้องมีการลง Plugin, Parser เพิ่มเติม ผู้สนใจลองดูลิ้งค์ในส่วนข้อมูลอ้างอิง

 

ข้อมูลอ้างอิง

One thought on “เพิ่มประสิทธิภาพการค้นหาข้อมูลใน MySQL ด้วย FULLTEXT SEARCH”

  1. ตอนนี้ได้ fulltext ไทยได้หรือยังครับ?

Leave a Reply

Your email address will not be published.