MySQL has supported full-text indexing since version 4.0, but the default minimum index word length is 4. For English, this default value is fairly reasonable. However, the vast majority of Chinese words consist of just 2 characters, meaning any word shorter than 4 characters cannot be indexed, rendering the full-text index feature practically useless. Most domestichosting providers likely haven’t noticed this issue and haven’t modified MySQL’s default settings.
Why Use Full-Text Indexing?
SQL Statement Optimization Tips
Most general-purposedatabase searches use SQL’s LIKE statement. The LIKE statement cannot utilize indexes; every query traverses from the first record to the last, resulting in extremely poor query efficiency. Generally, when data exceeds 100,000 records or there are too many concurrent users, LIKE queries can cause thedatabase to crash. This is precisely why manyapplications only offer title search — searching content is even slower and bogs down with just tens of thousands of records.
MySQL full-text indexing is specifically designed to solve fuzzy search problems. It pre-indexes entirearticles by word, delivering high search efficiency capable of supporting data retrieval across millions of records.
If you use your ownserver, make this configuration change immediately — don’t let this feature go to waste.
If you use shared hosting, contact your hosting provider right away to modify the configuration.First of all, this MySQL default value is a wrong setting for Chinese, and modifying it is simply correcting an error. Secondly, this configuration change is very simple and only takes a few minutes. Improving search efficiency also reduces the chance of the hosting provider’s database going down. If you forward this article to your hosting provider, I believe most of them will be willing to make the change.
Configuration Method:
Please ask your serveradministrator to modify my.ini (my.cnf on Linux), and add the line “ft_min_word_len=1” after the [mysqld] section. Then restart MySQL, and log into your websitebackend (Module Management -> Site-wide Search) to rebuild the full-text index. Otherwise, the site-wide search feature will not work.
Source: http://blog.ivz.com.cn/databases/mysql/66.html