Improve WordPress Search Performance with mysqlcft Chinese Full-Text Index on LNMP

Background and Problem

As a WordPress site's data grows, the search function can become a performance bottleneck. This is especially true when using fuzzy queries like SELECT ... WHERE ... LIKE '%...%'. MySQL cannot effectively use indexes for such queries, requiring full table scans that increase database load and impact overall server performance.

For full-text searches using SELECT ... WHERE ... LIKE '%...%' under high concurrency or with many database records, MySQL's efficiency is poor. When the query starts with wildcards % or _, indexes cannot be used, forcing a full table scan and placing significant pressure on the database. MySQL offers a built-in full-text index solution to improve performance and search quality. However, MySQL's native full-text indexing does not properly support Chinese.

For WordPress sites with Chinese content, a viable optimization is to use a third-party Chinese full-text index plugin, such as mysqlcft, to replace the default LIKE-based search.

Installing and Configuring the mysqlcft Chinese Full-Text Index Plugin

The following steps are based on an LNMP environment (e.g., using the LNMP one-click installation script) with MySQL 5.5.x. Plugin compatibility may vary with MySQL version; test in a staging environment first.

1. Environment Verification

MySQL must be dynamically compiled to load shared libraries (.so files). MySQL installed via the LNMP script typically meets this requirement.

2. Modify MySQL Configuration

Edit the MySQL configuration file my.cnf (usually at /etc/my.cnf or /usr/local/mysql/etc/my.cnf). Add the following parameter under the [mysqld] section:

ft_min_word_len = 1

This sets the minimum word length for full-text indexing to 1 byte, accommodating Chinese word segmentation. Restart MySQL for the change to take effect:

/etc/init.d/mysql restart
# or
systemctl restart mysqld

3. Download and Install the mysqlcft Plugin

Since the original Google Code project is archived, obtain the plugin from a reliable mirror or repository. Download the version matching your system architecture (32-bit or 64-bit).

After extraction, you should have the mysqlcft.so file.

4. Place the Plugin File

Log into the MySQL console to find the plugin directory:

mysql -uroot -p
SHOW VARIABLES LIKE 'plugin_dir';

Copy the mysqlcft.so file to the directory shown by the query, for example:

cp mysqlcft.so /usr/local/mysql/lib/plugin/

Ensure file permissions allow the MySQL process to read it.

5. Load the Plugin

Log into the MySQL console again and install the plugin:

INSTALL PLUGIN mysqlcft SONAME 'mysqlcft.so';

Verify the plugin is active:

SHOW PLUGINS;

You should see mysqlcft listed with status ACTIVE.

Note: To uninstall, first delete any full-text indexes created with the plugin, then run: UNINSTALL PLUGIN mysqlcft;

Creating Chinese Full-Text Indexes for WordPress Tables

After installing the plugin, create full-text indexes on core WordPress tables to optimize search for post content, titles, comments, etc.

Index Creation Commands

• Single-column full-text index:
  ALTER IGNORE TABLE `db_name`.`table_name` ADD FULLTEXT INDEX `index_name` (`column_name`) WITH PARSER mysqlcft;
• Multi-column combined full-text index:
  ALTER IGNORE TABLE `db_name`.`table_name` ADD FULLTEXT INDEX `index_name` (`col1`, `col2`) WITH PARSER mysqlcft;

Example Indexes for Core WordPress Tables

Replace db_name with your actual WordPress database name. Backup your database before proceeding.

1. Create a combined index on the posts table (wp_posts) for title and content:
   ALTER IGNORE TABLE `db_name`.`wp_posts` ADD FULLTEXT INDEX `post_fulltext_idx` (`post_title`, `post_content`) WITH PARSER mysqlcft;
   This may take time depending on data size.
2. Create an index on the meta_value field in the postmeta table (wp_postmeta):
   ALTER IGNORE TABLE `db_name`.`wp_postmeta` ADD FULLTEXT INDEX `meta_fulltext_idx` (`meta_value`) WITH PARSER mysqlcft;
3. Create an index on comment content in the comments table (wp_comments):
   ALTER IGNORE TABLE `db_name`.`wp_comments` ADD FULLTEXT INDEX `comment_fulltext_idx` (`comment_content`) WITH PARSER mysqlcft;

Rebuilding Indexes: If an index becomes corrupt, repair it with: REPAIR TABLE `db_name`.`table_name` QUICK;

Index Management and Querying

Viewing Indexes

View index information for a table:

SHOW INDEX FROM `db_name`.`table_name`;
-- or
SHOW KEYS FROM `db_name`.`table_name`;

Dropping Indexes

DROP INDEX `index_name` ON `db_name`.`table_name`;
-- or
ALTER TABLE `db_name`.`table_name` DROP INDEX `index_name`;

Querying with Full-Text Indexes

After creating indexes, use standard MySQL full-text search syntax. For example:

SELECT * FROM wp_posts WHERE MATCH(post_title, post_content) AGAINST('search term' IN BOOLEAN MODE);

Key Point: To make WordPress's core search use the new full-text indexes, you typically need to modify your theme files or use a dedicated search plugin (like Relevanssi) to replace the default LIKE queries. Modifying WordPress core files is not recommended as updates will overwrite changes.

Summary and Considerations

1. Compatibility: mysqlcft is an older plugin and may not be compatible with MySQL 5.7+. For newer MySQL versions, consider the built-in ngram full-text parser or dedicated search engines like Elasticsearch or Sphinx.
2. Testing: Thoroughly test plugin stability and search results in a staging environment before deploying to production.
3. Alternatives: If mysqlcft is unsuitable, consider configuring Sphinx or Elasticsearch as an external search engine for WordPress, using corresponding plugins for integration.
4. Monitoring: After creating full-text indexes, monitor server load and search response times to evaluate performance improvements.

Adding Chinese full-text indexes to WordPress database tables can significantly improve search query efficiency, reduce database CPU load, and enhance overall site performance.