Mysql Full Text Search

MySQL Full-text index

Note Not all engines support full-text indexing

MySQL most commonly used engine INnodb and myisam the latter support full-text retrieval the former does not support

Specify to retrieve columns 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 Index a column fulltext (Note_text) to establish a full-text index on a note_text column

Inserting data

Then use Match () to specify the column against () to specify the word
such as statements

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

Finding rows with a Hello word in the note_txt column returns a result of two rows

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

-Note that searches are not size-sensitive unless you use binary mode

    既然这样 为什么 不用  like语句呢  再来看上面例子  用like实现

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

Returns the same result as two rows

看采用全文搜索和like的返回结果   使用全文搜索的返回结果是已经排好序的   而 like的返回结果则没有排序主要是针对  hello出现在行的位置   全文结果中 第一个词  和 第三个词    like则没有按顺序排

MySQL is sorted primarily by rank

We can use the following method to view a column in a table in the rank of a word, 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 level is calculated by MySQL by the number of rows morphemes, the number of unique words, the total number of morphemes for the entire index, and the number of lines that contain the modified word. The result of a row that does not contain a word has a rank of 0 above it morphemes the previous rank value is higher than the following

Using Query extensions

When you want to find PP in Note_text, you know only one line from above if you use the following statement

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

return result is

note_textquster say ‘hello‘ to pp and he try again

If the extended query is used, the following three parts are divided into

• 1, first based on full-text search to find all rows as above the return result is only one row
• 2, MySQL retrieval above 1 so line, select useful words
• 3, MySQL again full-text search, this time also need to add 2 of the selected useful words as against words

select  note_text from  test_fulltext< span class= "keyword" style= "Color:rgb (0,0,255); Font-weight:bold ">where  match  ( Note_text) against ( ' pp '  with  query expansion);   

return results

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

such as PP would have a line containing hello so hello also as the keyword

Using Boolean queries

即使没有建立fulltext索引也能够用，但是速度非常慢 没有50%规则  （参见下 50%规则介绍）可以用包含特定意义的操作符，如 +、-、""，作用于查询字符串上。查询结果不是以相关性排序的。

such as statements

select  note_text from  test_fulltext< span class= "keyword" style= "Color:rgb (0,0,255); Font-weight:bold ">where  match  ( Note_text) against ( ' hello-pp* '  in  BOOLEAN MODE);   

A row that matches hello but does not contain PP has the result of

note_text‘hello‘ was said by quester  

Some notes and limitations of full-text search

• 1, only the MyISAM table supports
• 2, for most multibyte character sets, Columns that are full-text indexed must use the same character set and check code (collation).
• 3, ideographic language, such as Chinese, Japanese without a word delimiter (English separated by spaces each word), the full-text parser cannot determine the beginning and end of a word, Therefore, full-text search in MySQL is not supported.
• 4, in natural language retrieval, only those columns that are full-text indexed can be retrieved, and if you want to retrieve a column for multiple columns of an index, you must establish a separate full-text index for that column. Boolean retrievals can occur on non-indexed columns, but are slower.
• 5, against arguments must be constant strings.
• 6, the index does not record the position of the keyword in the string, the sorting algorithm is too singular.
• 7, if the index is not in memory, the retrieval speed is very slow, if it is a phrase query, you need to index and data in memory, otherwise the speed is very slow, So a larger key buffer is required. The index is also very slow when fragmented, so more frequent optimize table operations are required.
• 8, full-text indexing is slow for INSERT, UPDATE, delete. If you change 100 words, you need to perform 100 index operations instead of 1 times.

50% rules

     如果一个词出现在50%以上的行中，那么mysql将他作为一个非用词忽略   50%规则不适用于布尔查询    如果行数小于三行   则不返回结果  

Mysql Full Text Search