Mysql full-text search match against usage

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.
1. Prerequisites for using Mysql full-text search fulltext
The table type must be MyISAM.
The field type for full-text search must be char, varchar, and text.

2. Create an advanced configuration for full-text search
Because the default configuration of Mysql is that the index word length is 4, to support Chinese words, first change this.
* Unix users need to modify my. cnf. Generally, this file is stored in/etc/my. cnf. If not found, find/-name 'my. cnf 'first'
Add the following content to the [mysqld] location:
Ft_min_word_len = 2
Other attributes include
Ft_wordlist_charset = gbk
Ft_wordlist_file =/home/soft/mysql/share/mysql/wordlist-gbk.txt.
Ft_stopword_file =/home/soft/mysql/share/mysql/stopwords-gbk.txt.
A little explanation:
Ft_wordlist_charset indicates the character set of the dictionary, which currently supports (UTF-8, gbk, gb2312, big5)
Ft_wordlist_file is a Word Table file. Each line contains one word and its word frequency (separated by several tabs or spaces, dedicated for elimination)
Ft_stopword_file indicates filtering out non-indexed word lists, one row.
The minimum length of the word ft_min_word_len is added to the index. The default value is 4. To support Chinese words, change to 2.

3. Create a full-text search
The FullText keyword is used to identify the field in the TABLE under construction. The existing TABLE uses alter table (or create index) to CREATE an INDEX.
CREATE fulltext INDEX index_name ON table_name (colum_name );

4. Use full-text search
Use the MATCH function IN the WHERE clause of the SELECT statement. the keywords of the index are identified by AGAINST. in boolean mode only supports the keyword. You do not need to care about the position or whether it is the starting position.
SELECT * FROM articles where match (tags) AGAINST ('travel 'in boolean mode );

5. For details, please visit the Mysql official website.
Http://dev.mysql.com/doc/refman/5.1/zh/functions.html#fulltext-search
This is Mysql 5.1, but 4.x can also be used as a reference. Basically, Mysql 4.1 is used.

MySQL supports full-text indexing (Full-Text) It has been a long time. Currently, fulltext is an index type that only applies to the MyISAM table and has restrictions on defining the data types of index columns, only the following three types of combinations can be char, varchar, and text. Fulltext can be defined together when a table is created, or after the table is created, an index is appended using the statement alter table or create index. In short, the results are the same, however, there is a big difference in efficiency between the two. A large number of experiments prove that for a large number of tables, loading data first and then defining the full-text index is much faster than inserting a large amount of data into a table that has already defined the full-text index. Q: What is this? In fact, the principle is very simple. The former only requires one-time operation on your index list, sorting and comparison are completed in the memory, and then written to the hard disk; the latter will read the index table one by one on the hard disk and then compare the Final write. Naturally, the speed will be very slow.MySQLThe full-text index query function is implemented through the match () and against () functions. The field name in match () must be consistent with the field defined in fulltext. If you search in boolean mode, you can only include a field in fulltext and do not need to list it all. Against () defines the string to be searched and the mode in which the database is required to perform full-text index search. The following example describes the three-medium search modes supported by fulltext.

MySQL full-text index and Chinese Word Segmentation summary and general keyword search process
Http://www.tzlink.com/info/show.php? Aid = 4532.

Mysql full-text search Chinese Word Segmentation
Http://hi.baidu.com/agg230/blog/item/33d3d50eada260e337d1225b.html

Support Chinese MySQL 5.1 + full-text search and Word Segmentation plug-in
Http://hi.baidu.com/start_and_end/blog/item/6d6ab918b7d3800334fa412e.html

By using the search engine, you will find that word segmentation only occurs when the entire word hits 0.
For details about word segmentation, refer to the baidu search test results:

· If you search for "Xu zuningning", the result is "Xu zu" + "Ning ". (If you search for a person's name, it may have a hundred dictionaries with the last name. The first word after the last name is automatically prefixed)
· Search "Xu prefers" and the result is "Xu prefers ". (It means "would rather" be owned by "Xu. Same as above. Because Xu is the surname .)
· Search "Xu zu prefers" and the result is "Xu zu" + "yes ". (Because "preferred" is the word, "Xu" only takes "Zu ".)
· Search for "Xu zening GAO" and the result is "Xu zening ". (Because "Ning Gao" is not a keyword, "Ning" is owned by the prefix. However, "high" may be omitted because it is a single word to improve the efficiency of word search .)