MySQL FULLTEXT Search with MATCH AGAINST for WordPress

MySQL MATCH() AGAINST() full-text search is orders of magnitude faster than LIKE '%keyword%' for large tables — it uses an inverted index, returns relevance scores, and supports phrase and boolean queries. WordPress’s default search uses LIKE, but you can replace it with full-text search for better results.

Problem: The default WordPress search queries post_title and post_content with a LIKE '%keyword%' pattern — this does full table scans, ignores word relevance, and returns poor results for multi-word searches.

Solution: Add a FULLTEXT index on wp_posts(post_title, post_content) and use MATCH(post_title, post_content) AGAINST('keyword' IN BOOLEAN MODE) in a custom query. Hook into posts_search and posts_orderby to replace the LIKE pattern with FULLTEXT in the standard WordPress search.

The examples below create a full-text index on wp_posts, run natural language and boolean mode queries, and hook the WordPress search to use MATCH AGAINST via a posts_search filter.

-- 1. Create a FULLTEXT index on post_title and post_content
-- InnoDB supports FULLTEXT since MySQL 5.6
ALTER TABLE wp_posts
  ADD FULLTEXT INDEX ft_post_search (post_title, post_content);

-- 2. Natural Language Mode (default) — relevance ranked
SELECT ID, post_title,
       MATCH(post_title, post_content) AGAINST ('wordpress performance') AS score
FROM wp_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
  AND MATCH(post_title, post_content) AGAINST ('wordpress performance')
ORDER BY score DESC
LIMIT 10;

-- 3. Boolean Mode — supports +/- operators and wildcards
SELECT ID, post_title
FROM wp_posts
WHERE post_status = 'publish'
  AND MATCH(post_title, post_content)
      AGAINST ('+wordpress +caching -plugin' IN BOOLEAN MODE);
-- +word  must include
-- -word  must exclude
-- word*  prefix wildcard
-- "phrase"  exact phrase

-- 4. Check minimum word length (default 4 for InnoDB)
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
-- Words shorter than this are ignored; set in my.cnf if needed:
-- [mysqld]
-- innodb_ft_min_token_size = 3

Replace WordPress default LIKE search with MATCH AGAINST:

// Replace WordPress LIKE search with FULLTEXT MATCH AGAINST
add_filter( 'posts_search', function( string $search, WP_Query $query ): string {
    global $wpdb;
    if ( ! $query->is_search() || ! $query->is_main_query() ) return $search;

    $term = $query->get( 's' );
    if ( empty( $term ) ) return $search;

    // Sanitize: remove characters that would break AGAINST syntax
    $safe = esc_sql( $wpdb->esc_like( $term ) );

    return $wpdb->prepare(
        " AND MATCH({$wpdb->posts}.post_title, {$wpdb->posts}.post_content) AGAINST (%s IN BOOLEAN MODE)",
        $safe . '*'  // trailing wildcard for prefix matching
    );
}, 10, 2 );

// Also remove the default LIKE-based orderby and use relevance score
add_filter( 'posts_orderby', function( string $orderby, WP_Query $query ): string {
    global $wpdb;
    if ( ! $query->is_search() || ! $query->is_main_query() ) return $orderby;
    $term = $query->get( 's' );
    if ( empty( $term ) ) return $orderby;
    return $wpdb->prepare(
        "MATCH({$wpdb->posts}.post_title, {$wpdb->posts}.post_content) AGAINST (%s) DESC",
        $term
    );
}, 10, 2 );

NOTE: Rebuild the full-text index after importing a large dataset with OPTIMIZE TABLE wp_posts;. Also note that full-text search is case-insensitive and uses the table's character set collation — utf8mb4_unicode_ci works correctly for most languages.