MySQL Full-Text search

Full-text indexing is an fulltext type index in MySQL. The fulltext index is used for MyISAM tables, which can be created on a char, varchar, or text column by using ALTER TABLE or CREATE index at or after the CREATE table. For large databases, it is very quick to load the data into a table with no Fulltext index and then use ALTER TABLE (or CREATE index) to create the index. It will be very slow to load data into a table that already has a fulltext index.

Full-Text search is done through the match () function.

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.00 sec)
Mysql> INSERT into articles VALUES
-> (NULL, ' MySQL Tutorial ', ' DBMS stands for DataBase ... '),
-> (NULL, ' How to use the MySQL efficiently ', ' after you went through a ... '),
-> (NULL, ' Optimising MySQL ', ' in this tutorial we'll show ... '),
-> (NULL, ' 1001 MySQL Tricks ', ' 1. Never run mysqld as root. 2... '),
-> (NULL, ' MySQL vs. Yoursql ', ' in the following database comparison ... '),
-> (NULL, ' MySQL security ', ' when configured properly, MySQL ... ');
Query OK, 6 rows Affected (0.00 sec)
Records:6 duplicates:0 warnings:0
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) function match () performs a natural language search for a string against a text set (a set of columns containing one or more columns in a fulltext index). The search string as a against () parameter is given. Searches are performed in a way that ignores the case of letters. MATCH () returns a dependency value for each record row in the table. That is, the similarity scale between the search string and the text of the column that the record row is specified in the match () list.

When match () is used in a WHERE clause (see the example above), the returned row of records is automatically sorted in the order of relevance from the highest to the bottom. The correlation value is a non-negative floating-point number. 0 correlation means not similar. Dependencies are calculated based on the number of words in the record line, the number of unique words in the row, the total number of words in the set, and the number of documents (record lines) that contain a special word.

It can also perform a search in a logical mode. This is described in the following sections.

The preceding example is a basic description of the use of the function match (). The rows of records are returned in descending order of similarity.

The next example shows how to retrieve a clear similarity value. If there is no where and there is no ORDER BY clause, the return row is not sorted.

mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|　1 |　　　　　　　　　　　　0.64840710366884 |
|　2 |　　　　　　　　　　　　　　　　　　　 0 |
|　3 |　　　　　　　　　　　　0.66266459031789 |
|　4 |　　　　　　　　　　　　　　　　　　　 0 |
|　5 |　　　　　　　　　　　　　　　　　　　 0 |
|　6 |　　　　　　　　　　　　　　　　　　　 0 |
+----+-----------------------------------------+

6 rows in Set (0.00 sec) The following example is a bit more complicated. The query returns similarity and still returns the row of records in the order of decreasing similarity. In order to complete this result, you should specify match () two times. This does not cause additional overhead because the MySQL optimizer notices the same match () call two times and invokes only one Full-text search code.

mysql> SELECT id, body, MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root') AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body　　　　　　　　　　　　　　　　| score　　　　　 |
+----+-------------------------------------+-----------------+
|　4 | 1. Never run mysqld as root. 2. ... | 1.5055546709332 |
|　6 | When configured properly, MySQL ... |　 1.31140957288 |
+----+-------------------------------------+-----------------+