MySQL full-text search _ MySQL

MySQL full-text search full-text index is a FULLTEXT index in MySQL. FULLTEXT indexes are used for MyISAM tables. you can use alter table or create index to CREATE them on CHAR, VARCHAR, or TEXT columns at or after create table. For large databases, it is very fast to load data to a TABLE without FULLTEXT indexes and then CREATE an INDEX using alter table (or create index. Loading data to a table with FULLTEXT indexes will be very slow.

Full-text search is completed using the MATCH () function.

Mysql> create table articles (
-> Id int unsigned AUTO_INCREMENT not null primary key,
-& Gt; 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 MySQL Efficiently ', 'After you went through ...'),
-> (NULL, 'Optimising mysql', 'In this tutorial we will 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 () executes a natural language search for a string against a text set (a column set that contains one or more columns in a FULLTEXT index. The search string is given as a parameter of AGAINST. Search to ignore uppercase/lowercase letters. MATCH () returns a correlation value for each record row in the table. That is, the similarity scale between the text of the specified column in the MATCH () list of the search string and Record Row.

When MATCH () is used in a WHERE clause (see the preceding example), the returned record rows are automatically sorted in the descending order of relevance. The correlation value is a non-negative floating point number. Zero correlation means they are not similar. Correlation is calculated based on the number of words in the Record Row, the number of unique words in the row, the total number of words in the set, and the number of documents (Record Row) that contain a special word.

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

The preceding example describes the usage of the MATCH () function. Record rows are returned in descending order of similarity.

The next example shows how to retrieve a specific similarity value. If neither the WHERE clause nor the order by clause exists, the returned rows are not sorted.

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