Word searching in MySQL

Word searching in MySQL

A quick guide on how to create a table with full-text search indexing in MySQL and perform word searches using the MATCH AGAINST function.

How to create a table with word search indexing.

mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES
    -> ('MySQL has now support', 'for full-text search'),
    -> ('Full-text indexes', 'are called collections'),
    -> ('Only MyISAM tables', 'support collections'),
    -> ('Function MATCH AGAINST()', 'is used to do a search'),
    -> ('Full-text search in MySQL', 'implements vector space model');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL');
+---------------------------+-------------------------------+
| a | b |
+---------------------------+-------------------------------+
| MySQL has now support | for full-text search |
| Full-text search in MySQL | implements vector-space-model |
+---------------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT *, MATCH (a,b) AGAINST ('collections support') as x FROM t;
+---------------------------+-------------------------------+--------+
| a | b | x |
+---------------------------+-------------------------------+--------+
| MySQL has now support | for full-text search | 0.3834 |
| Full-text indexes | are called collections | 0.3834 |
| Only MyISAM tables | support collections | 0.7668 |
| Function MATCH AGAINST() | is used to do a search | 0 |
| Full-text search in MySQL | implements vector space model | 0 |
+---------------------------+-------------------------------+--------+
5 rows in set (0.00 sec)