Full text search in suo-PHP site, whether mysql should add FULLTEXT, how is the efficiency compared with LIKE?

In the past, wheretitlelike & #039; % $ kw % & #039; ORcontentlike & #039; % $ kw % & #039; was used to make a PHP site search, FULLTEXT has never been used. The data table is MYISAM. 1) Is it necessary to use FULLTEXT to Improve the efficiency? Wherematch (title, con... used to make a PHP site search. where title like '%$kw%' OR content like '%$kw%'This mode has never been used. FULLTEXT, Data table is MYISAM.
1) Is it necessary to use FULLTEXT, Will the efficiency be improved? where match(title,content) against ('$kw')
2) What is the impact on the data table? Increase storage space? Slow query?

The info table contains 0.2 million data records.

titleVarchar (60) NOT NULL
contentText NOT NULL

Problem

Just found that Yii2 does not seem to support fulltext? PDO is used by default in yii2 built-in db. Does PDO support fulltext?

Reply content:

Previously, PHP site search was used.where title like '%$kw%' OR content like '%$kw%'This mode has never been used.FULLTEXT, Data table isMYISAM.
1) Is it necessary to useFULLTEXT, Will the efficiency be improved?where match(title,content) against ('$kw')
2) What is the impact on the data table? Increase storage space? Slow query?

The info table contains 0.2 million data records.

titleVarchar (60) NOT NULL
contentText NOT NULL

Problem

Just found that Yii2 does not seem to support fulltext? PDO is used by default in yii2 built-in db. Does PDO support fulltext?

Chinese Word Segmentation is more complex than English word segmentation,
Mysql FULLTEXT does not support Chinese word segmentation, so you should use engines such as sphenders and solr.

Http://www.sqlite.org/fts3.html
In the official test of SQLite, it takes more than 0.5 million seconds to use LIKE '% keyword %' for 22.5 pieces of data to perform fuzzy search, and only 0.03 seconds to use MATCH 'keyword' for full-text search, which is 749 times faster than fuzzy search.

FULLTEXT can use indexes for queries, which must be much faster than LIKE fuzzy search.
InnoDB after MySQL5.6.4 supports full-text Chinese search.
Https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

For example, you need to perform full-text search for all the articles on your blog:

CREATE TABLE articles (    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    title VARCHAR(200),    content TEXT,    article_fc TEXT,    FULLTEXT idx (article_fc)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When inserting an article, perform SCWS Chinese word segmentation on the title and body content fields and separate them with spaces to save a field for full-text search, such as article_fc. This field requires a full-text FullText index.

When you search, use SCWS to perform word segmentation for the input, for example, to obtain the keywords word1 and word2, and then use the match against statement to perform full-text search:

SELECT * FROM articles WHERE MATCH(article_fc) AGAINST('word1 word2');

The table in which the article_fc field is located can also be separated from the article table in which the title body is located. Connect to the article table to read the title body.

SCWS is a php pecl Chinese Word Segmentation extension developed by Ma minglian hightman. It provides dictionaries. If the PECL extension cannot be installed, the author also provides a PHP-implemented Chinese Word Segmentation dictionary PSCWS:
Http://www.xunsearch.com/scws/

If you do not want to use FULLTEXT of the database, try the open-source Chinese search engine XunSearch developed by SCWS author ma minglian:
Http://www.cloud-sun.com/view/product
[Performance] XunSearch supports a maximum of 4 billion data records in a single database. The Retrieval time of approximately 0.5 billion TB of data on 1.5 web pages cannot exceed 1 second (non-Cache ).
[Easy to use] the front-end is a development kit written in the scripting language PHP. The API is simple and clear, and it is extremely difficult to develop. It provides sample code, documents, and auxiliary script tools in Chinese.
[Rich functions] in addition to basic custom word segmentation, field search, and Boolean search, it also directly supports professional functions such as search, pinyin search, and search recommendations that users urgently need.

As mentioned above, mysql is not suitable for such tasks.

Sphtracing, solr, lucene, elasticsearch, etc.

After a full-text index is added to a table, the storage space must be increased, and the insertion speed may be affected. The full-text search still does not use MYSQL, and the query will not be very fast after fulltext is added. You can use solr for PHP. PHP has corresponding extensions.

Xunsearch. If you think the segment search is good.

Http://www.9958.pw/post/coreseek_sphinx. This may be helpful.