Implement Full-Text Search in WordPress with MySQL MATCH AGAINST

Home » MySQL » Implement Full-Text Search in WordPress with MySQL MATCH AGAINST
July 20, 2023 MySQL
Implement Full-Text Search in WordPress with MySQL MATCH AGAINST

WordPress’s default search uses LIKE '%keyword%' queries against wp_posts.post_title and wp_posts.post_content — a pattern that cannot use B-tree indexes, causes full table scans on large databases, and returns results sorted by date rather than relevance. MySQL’s FULLTEXT index and MATCH() AGAINST() syntax replace this with an inverted word index that scores results by term frequency, inverse document frequency (TF-IDF), and proximity — returning the most relevant posts first while being orders of magnitude faster on tables with tens of thousands of rows. A FULLTEXT index is created with ALTER TABLE wp_posts ADD FULLTEXT INDEX ft_content (post_title, post_content) — covering both columns in a single index allows a single MATCH(post_title, post_content) expression that scores matches across both. InnoDB supports FULLTEXT indexes since MySQL 5.6 and does not require MyISAM conversion. Natural language mode (IN NATURAL LANGUAGE MODE) is the default and works well for user-facing search — it ignores stop words (common words like “the”, “and”), handles word stemming, and assigns higher relevance scores to rare terms. Boolean mode (IN BOOLEAN MODE) supports operators: + requires a term, - excludes it, * is a wildcard suffix, and "phrase" matches an exact phrase — useful for admin search interfaces where power users need precise control. The minimum word length for indexing is controlled by innodb_ft_min_token_size (default 3) — words shorter than this threshold are not indexed and will not match; lowering this to 2 indexes two-character words like CSS or JS. WordPress’s search query is replaceable via the posts_search and posts_orderby filters, which allow injecting a MATCH AGAINST expression as a calculated relevance column without replacing the entire query. The database optimization post covers the ANALYZE TABLE step that should follow a FULLTEXT index creation to update query optimizer statistics.

Problem: A WordPress blog with 15,000 posts has search response times exceeding 4 seconds because the default LIKE '%keyword%' query scans every row in wp_posts, and results are sorted by date instead of relevance — visitors searching for a specific topic get old, tangentially related posts at the top.

Solution: Create a FULLTEXT index on post_title and post_content, hook into posts_search and posts_orderby filters to replace the LIKE clause with a MATCH AGAINST expression, and pass the relevance score as the sort column.

-- Create the FULLTEXT index (run once — takes seconds on most WP databases)
ALTER TABLE wp_posts
    ADD FULLTEXT INDEX ft_post_search (post_title, post_content);

-- Test in natural language mode (returns rows scored by relevance)
SELECT ID, post_title,
       MATCH(post_title, post_content)
       AGAINST('redis caching performance' IN NATURAL LANGUAGE MODE) AS relevance
FROM   wp_posts
WHERE  post_status = 'publish'
  AND  post_type   = 'post'
  AND  MATCH(post_title, post_content)
       AGAINST('redis caching performance' IN NATURAL LANGUAGE MODE)
ORDER  BY relevance DESC
LIMIT  10;

-- Boolean mode: require "WordPress" and exclude "plugin"
SELECT ID, post_title
FROM   wp_posts
WHERE  post_status = 'publish'
  AND  MATCH(post_title, post_content)
       AGAINST('+WordPress -plugin' IN BOOLEAN MODE);

-- Check the index exists
SHOW INDEX FROM wp_posts WHERE Key_name = 'ft_post_search';

// Replace WP default LIKE search with MATCH AGAINST via query filters
add_filter('posts_search', 'myplugin_fulltext_search', 10, 2);
add_filter('posts_orderby', 'myplugin_fulltext_orderby', 10, 2);

function myplugin_fulltext_search(string $search, WP_Query $query): string {
    if (!is_search() || !$query->is_main_query()) return $search;

    global $wpdb;
    $term = $query->get('s');
    if (!$term) return $search;

    // Use prepare() — the search term is a user-supplied value
    $search = $wpdb->prepare(
        " AND MATCH({$wpdb->posts}.post_title, {$wpdb->posts}.post_content)
              AGAINST(%s IN NATURAL LANGUAGE MODE)",
        $term
    );
    return $search;
}

function myplugin_fulltext_orderby(string $orderby, WP_Query $query): string {
    if (!is_search() || !$query->is_main_query()) return $orderby;

    global $wpdb;
    $term = $query->get('s');
    if (!$term) return $orderby;

    $relevance = $wpdb->prepare(
        "MATCH({$wpdb->posts}.post_title, {$wpdb->posts}.post_content)
         AGAINST(%s IN NATURAL LANGUAGE MODE) DESC",
        $term
    );
    return $relevance;
}

NOTE: FULLTEXT indexes in InnoDB do not index words shorter than innodb_ft_min_token_size (default 3 characters) or words on the stop-word list. To search for two-letter acronyms like “JS” or “AI”, set innodb_ft_min_token_size=2 in my.cnf and rebuild the index with OPTIMIZE TABLE wp_posts. The stop-word list can be customized or disabled with innodb_ft_enable_stopword=0 — useful for technical blogs where common words like “update” or “version” carry meaningful search intent.

Related posts