ค้นหาข้อความเร็วใน MySQL ด้วย Full-text Search

ใช้ดัชนี FULLTEXT และ MATCH() ... AGAINST สำหรับค้นหาข้อความเร็วกว่า LIKE

mysql> CREATE TABLE DemoTable1554 (
    -> Title TEXT
    -> );
Query OK, 0 rows affected (0.63 sec)

mysql> CREATE FULLTEXT INDEX faster_title ON DemoTable1554(Title);
Query OK, 0 rows affected, 1 warning (7.09 sec)

mysql> INSERT INTO DemoTable1554 VALUES('John is working on MySQL database');
Query OK, 1 row affected (0.26 sec)
mysql> INSERT INTO DemoTable1554 VALUES('Adam Smith is working on Java language');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DemoTable1554 VALUES('John smith is working on Python Language');
Query OK, 1 row affected (0.17 sec)

mysql> SELECT * FROM DemoTable1554;

+------------------------------------------+
| Title                                    |
+------------------------------------------+
| John is working on MySQL database        |
| Adam Smith is working on Java language   |
| John smith is working on Python Language |
+------------------------------------------+
3 rows in set (0.00 sec)

ค้นหาคำว่า "language" แบบ Boolean mode:

mysql> SELECT * FROM DemoTable1554 WHERE MATCH(Title) AGAINST('language' IN BOOLEAN MODE);

+------------------------------------------+
| Title                                    |
+------------------------------------------+
| Adam Smith is working on Java language   |
| John smith is working on Python Language |
+------------------------------------------+
2 rows in set (0.00 sec)

ข้อดี: เร็วมากสำหรับข้อความยาว รองรับ Boolean operators (+ - * ~) และ Relevance ranking