Mysql full text search and Mysql full text search

Mysql full text search and Mysql full text search
Mysql full-text index

Note that not all engines Support full-text retrieval.

Mysql's most common engines, INnodb and myisam, support full-text retrieval. The former does not support full-text retrieval.

Specify the columns to be retrieved when creating a table

CREATE TABLE TEST_FULLTEXT(note_id int not null auto_increment,note_text text null,primaty key(note_id),FULLTEXT(note_text))engine=myisam;

Fulltext indexes fulltext (note_text) of a column and creates a full-text index on a note_text column.

Insert data

Then, use match () to specify the column Against () to specify the word
Such as statement

select *from TEST_FULLTEXTwhere Match(note_text) Against('hello');

The result returned by the row containing the hello word in the note_txt column is two rows.

note_text'hello' was said by quester quster say 'hello' to pp and he try again

-Note that the search is case insensitive unless the BINARY method is used.

In this case, why not use the like statement? Let's look at the above example and use like to implement it.

select *from TEST_FULLTEXTwhere note_text like '%hello%';

Returns two rows in the same way.

note_textquster say 'hello' to pp and he try again'hello' was said by quester 

The results returned by full-text search and like are sorted, while those returned by like are not sorted. This is mainly for the first place in the full-text results where hello appears in the row. and the third word like is not arranged in order

Mysql is mainly sorted by level.

We can use the following method to view the level of a certain word in a table, and continue to use the above example.

select note_text, Match(note_text) Aginst('hello') as rannkfrom TEST_FULLTEXT

The output is as follows:

 note_text                                             rank fhgjkhj                                                0 fdsf shi jian                                          0 quster say 'hello' to pp and he try again           1.3454876123454 huijia quba                                            0 'hello' was said by quester                         1.5656454547876

The calculation of the level is based on the number of words in the row, the number of unique words, the total number of words in the entire index, and the number of rows containing modified words. 0 in the result above, the level of the word in the front is higher than that in the back

Use query Extension

When you want to search for pp in note_text, you know from the above that there is only one row. If you use the following statement

select note_text from test_fulltextwhere match(note_text) against('pp');

The returned result is

note_textquster say 'hello' to pp and he try again

If you use extended query, it can be divided into the following three parts:

• 1. First, search for all rows based on the full text. The returned result is only one row.
• 2. mysql retrieves the preceding line and selects useful words.
• 3. mysql searches the full text again. This time, you must add the Useful Words selected in step 2 as the words in against.

select note_text from test_fulltextwhere match(note_text) against('pp' with query expansion);

Returned results

note_textquster say 'hello' to pp and he try again'hello' was said by quester                        

For example, if the original pp row contains "hello", "hello" is also used as a keyword.

Use Boolean Query

Even if you do not have a fulltext index, it is sufficient, but the speed is very slow. There are no 50% rules (see section 50% Rules). You can use operators with specific meanings, such as +,-, and ,-,"", the query string. Query results are not sorted by correlation.

Such as statement

select note_text from test_fulltextwhere match(note_text) against('hello -pp*' IN BOOLEAN MODE );

Indicates that the row that matches hello but does not contain pp is

note_text'hello' was said by quester  

Description and restrictions of full-text search

• 1. Only MyISAM tables are supported.
• 2. For most multi-byte character sets, full-text indexed columns must use the same character set and check code (collation ).
• 3. ideographic languages, such as Chinese and Japanese, do not have word delimiters (each word is separated by spaces in English). The full-text analyzer cannot determine the start and end of a word, therefore, full-text search in MySQL is not supported.
• 4. in natural language search, only the full-text indexed columns can be retrieved. To search multiple indexed columns for one column, you must create a full-text index for this column. Boolean search can be performed on non-indexed columns, but it will be slower.
• 5. The parameters after against must be constant strings.
• 6. The index does not record the position of the keyword in the string, and the Sorting Algorithm is too single.
• 7. If the index is not in the memory, the retrieval speed will be very slow. If it is a phrase query, the indexes and data must be in the memory. Otherwise, the retrieval speed will be very slow, so a larger key buffer is required. The index fragmentation is slow, so more frequent optimize table operations are required.
• 8. Full-text indexes are slow for insert, update, and delete operations. For example, you need to perform 100 Index operations instead of 1 index operation to change 100 words.

50% rules

If a word appears in more than 50% rows, mysql will ignore it as a non-word. Rule 50% is not applicable to Boolean queries. If the number of rows is smaller than three rows, no results will be returned. See Rule 50%.