MySQL full-text search explained with WordPress database examples

Home » Coding » MySQL full-text search explained with WordPress database examples
April 10, 2022 Coding, MySQL
MySQL full-text search explained with WordPress database examples

WordPress’s built-in search uses LIKE ‘%keyword%’ queries against post_title and post_content, which force a full table scan on every search request. On sites with thousands of posts this quickly becomes a significant performance problem. MySQL’s full-text search engine offers a purpose-built alternative: you add a FULLTEXT index to the columns you want to search, and then use the MATCH(columns) AGAINST(‘query’) syntax to query them. Full-text search is dramatically faster than LIKE because MySQL maintains an internal inverted index of all words in the indexed columns, similar to how a search engine works. It also supports relevance scoring — MATCH ... AGAINST returns a floating-point score for each row indicating how well it matches the query, so you can ORDER BY that score to show the most relevant results first. MySQL supports three full-text search modes: natural language mode (the default), boolean mode (supports +, -, * operators), and query expansion mode (broadens results using words from the top matches). The minimum word length that gets indexed is controlled by the ft_min_word_len system variable (default 4) for MyISAM and innodb_ft_min_token_size (default 3) for InnoDB. WordPress uses InnoDB for all its tables. Adding a FULLTEXT index to an existing WordPress table is safe and online — it does not lock the table for reads. You can hook into posts_search and posts_search_orderby to replace WordPress’s default search SQL with a full-text query. Review the MySQL indexes guide and the EXPLAIN guide before adding indexes to understand the performance tradeoffs.

Problem: WordPress search uses LIKE ‘%keyword%’ queries that scan the entire wp_posts table and return results in arbitrary order, making search slow and irrelevant on large sites.

Solution: Add a FULLTEXT index to post_title and post_content, then replace the default search SQL using WordPress filters:

-- 1. Add a FULLTEXT index to wp_posts (runs online, no table lock)
ALTER TABLE wp_posts
  ADD FULLTEXT INDEX ft_search (post_title, post_content);

-- 2. Natural language search — returns relevance score
SELECT ID, post_title,
       MATCH(post_title, post_content) AGAINST('WordPress caching') AS score
FROM   wp_posts
WHERE  post_status = 'publish'
  AND  post_type   = 'post'
  AND  MATCH(post_title, post_content) AGAINST('WordPress caching')
ORDER  BY score DESC
LIMIT  10;

-- 3. Boolean mode — find posts with 'cache' but NOT 'redis'
SELECT ID, post_title
FROM   wp_posts
WHERE  post_status = 'publish'
  AND  post_type   = 'post'
  AND  MATCH(post_title, post_content)
       AGAINST('+cache -redis' IN BOOLEAN MODE)
LIMIT  10;

-- 4. Wildcard search in boolean mode (match 'optim*' → optimize, optimization)
SELECT ID, post_title
FROM   wp_posts
WHERE  post_status = 'publish'
  AND  MATCH(post_title, post_content)
       AGAINST('optim*' IN BOOLEAN MODE)
LIMIT  10;

// Hook into WordPress search to use FULLTEXT instead of LIKE
add_filter( 'posts_search', 'ha_fulltext_search', 10, 2 );
add_filter( 'posts_search_orderby', 'ha_fulltext_orderby', 10, 2 );

function ha_fulltext_search( $search, WP_Query $query ) {
    if ( ! $query->is_search() || ! $query->is_main_query() ) {
        return $search;
    }
    global $wpdb;
    $term   = $query->get( 's' );
    $search = $wpdb->prepare(
        " AND MATCH({$wpdb->posts}.post_title, {$wpdb->posts}.post_content)
          AGAINST(%s IN BOOLEAN MODE)",
        $term . '*'
    );
    return $search;
}

function ha_fulltext_orderby( $orderby, WP_Query $query ) {
    if ( ! $query->is_search() || ! $query->is_main_query() ) {
        return $orderby;
    }
    global $wpdb;
    $term = $query->get( 's' );
    return $wpdb->prepare(
        "MATCH({$wpdb->posts}.post_title, {$wpdb->posts}.post_content)
         AGAINST(%s IN BOOLEAN MODE) DESC",
        $term . '*'
    );
}

NOTE: MySQL full-text search ignores words shorter than innodb_ft_min_token_size (default 3 characters) and common stopwords defined in innodb_ft_server_stopword_table. If a search term is always returning zero results, check whether it is in the stopword list with SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;. After adding or changing the FULLTEXT index, rebuild it with OPTIMIZE TABLE wp_posts; to ensure all rows are indexed. The safe $wpdb queries guide explains how to always use $wpdb->prepare() to prevent SQL injection in custom search queries.

Related posts