Adding MySQL FULLTEXT Support to WordPress

Adding MySQL FULLTEXT Support to WordPress

FULLTEXT support to WordPress is simple if you have access to phpMyAdmin. If not, it can still be done but is a little trickier if you’re not used to command-line. We’ll demonstrate how to modify your WordPress database using phpMyAdmin.

How To Modify Database

  1. Start out by having a set of keywords to test against. You’ll need ‘before’ and ‘after’ test results. Also, I can’t stress this enough but back up your database before proceeding!

  2. Next, log into your phpMyAdmin page and navigate to your {prefix}_posts table.

  3. Click on the “Structure” tab.

  4. Scroll down until you see the “Indexes” section. Below that section there should be an area that allows you to create an index on a specified number of fields. Enter “2” in the text box and click “Go.”

  5. Give your index a relevant name, like “wp_fulltext” and then select “FULLTEXT” from the Index-type drop-down.

  6. Select post_title and post_content for your two fields. You can also add additional fields using this section.

  7. Click “Save.” You should receive a success message. If not, check the data-type of your table. It should be MyISAM, not InnoDB.

It should be that simple — and yes, there’s more. You’ll need to modify the default WordPress MySQL queries responsible for search results.

Adding Filters

The filters we’re going to be working with are called posts_search and posts_fields. In your functions.php file, add the following line: add_filter('posts_search', '_my_posts_search_function', 12, 2);

Now, we’re going to add our function:

<?php
function _my_posts_search_function($sql, $query)
{
    global $wpdb;
    $keyword = $query->get('s');
    if (!$keyword || $sql == '') {
        return $sql;
    }

    $keyword_wildcard = "{$keyword}*";
    $mode = "";
    if (strlen($keyword) <= 3) {
        $mode = " IN BOOLEAN MODE";
    }
    $sql = " AND MATCH ({$wpdb->posts}.post_title, {$wpdb->posts}.post_content) AGAINST ('%s'{$mode}) ";
    return $wpdb->prepare($sql, $keyword_wildcard);
}

What we’re doing here is modifying the default search query by adding the MATCH/AGAINST statement containing our index columns (post_title and post_content) and our keyword to the WHERE clause. We add a wildcard (*) to the keyword to increase the number of search results. We also check if the keyword is less than 3 letters. If the keyword is less than three letters, we add IN BOOLEAN MODE as part of the AGAINST query. Without BOOLEAN MODE, keywords that are less than three letters will return no results.

That’s it! You’re done. You may or may not see a change in the way results are returned. This is why it’s important to do results tests before these changes and after. By default, WordPress uses the LIKE statement.

Performance

As for performance, on a small database you won’t notice a difference. For larger databases, it is hard to say. MATCH may be faster due to the way WordPress strings OR operators together with LIKE ‘%keyword%’. My advice is to do your own speed-testing and draw your own conclusions

Caveats

If your tables are InnoDB, you will need to convert them to MyISAM before creating the FULLTEXT index. If you’re already on the Structure tab, navigate to the Operations tab. Under “Table Options,” select MyISAM and click “Go.” Provided you have the right permissions, you should receive a success message.

The MySQL stopword list can present a problem when using FULLTEXT search. The stopword list is a list of commonly used words that MySQL uses to determine valid results. If MySQL detects these words in a search query, it ignores them. More information on MySQL stopwords can be found in the MySQL Reference Manual.

Now that you have FULLTEXT search working, you can add a custom orderby parameter by modifying the SELECT statement using the posts_fields filter.

Relevance ordering doesn't work very well with BOOLEAN MODE. When you view your query in PHPMyAdmin, you will notice that the relevance column returns “1” for each row. Without BOOLEAN MODE, each value will be a decimal — which allows you to order the results accordingly. However, eliminating BOOLEAN MODE means that you will not be able to run queries on search terms that are less than 3 characters.

Adding ‘relevance’ Column to Query

To be able to modify the order that posts are returned in according to relevance, you must add a new column to the search query. Since by default the relevance column doesn't exist, you'll need to create it by adding a new sub-query to the SELECT statement and casting it as ‘relevance,’ example:

SELECT *, MATCH (post_title, post_content) AGAINST ('keyword' IN BOOLEAN MODE) AS relevance FROM wp_posts

To accomplish this, you will need to use the posts_fields filter to modify the fields returned in the SQL query.

<?php
/**
 * Add MATCH fields to SELECT statement on search queries
 *
 * @param string $sql SQL string
 * @param WP_Query $query WP_Query object passed by reference
 * @return string Modified SQL string
 */
function _my_posts_fields_function ($sql, $query = null)
{
    global $wpdb;
    $keyword = $query->get('s');
    if ($keyword === '')
        return $sql;
    $keyword_wildcard = "{$keyword}*";
    $mode = "";
    if (strlen($keyword) <= 3) {
        $mode = " IN BOOLEAN MODE";
    }
    $sql = $sql . ", MATCH ({$wpdb->posts}.post_title, {$wpdb->posts}.post_content) AGAINST ('%s'{$mode}) AS relevance";
    return $wpdb->prepare($sql, $keyword_wildcard);
}

Yes, it is approximately the same code as what we used to modify the WHERE statement in Part I, but with some minor differences because we're adding to the SELECT statement. A better approach would be to move the duplicated code into its own function (in the example above, that would be lines 13 thru 21). Anyway, we're not done yet. Next, you'll need to add the ‘relevance’ column to the ORDER statement.

Modifying ORDER statement

Modifying the ORDER statement portion of the SQL query that WP_Query uses is pretty straightforward. Fortunately, WordPress provides a filter to allow us to change the ORDER parameters. It is called posts_orderby. One thing to be aware of is that this will override the orderby parameter if you’re modifying the search loop.

<?php
/**
 * Modify ORDER BY statement if orderby parameter is relevance
 *
 * @param string $sql
 * @param WP_Query $query
 *
 * @return string Modified query string
 */
function _my_posts_orderby_function ($sql, $query)
{
    if (!$query->get('s'))
        return $sql;
    $sql = " relevance {$query->query_vars['order']}";
    return $sql;
}

That's it!

References

Rawposts-fields-function.php

<?php
/**
 * Add MATCH fields to SELECT statement on search queries
 *
 * @param string $sql SQL string
 * @param WP_Query $query WP_Query object passed by reference
 * @return string Modified SQL string
 */
function _my_posts_fields_function ($sql, $query = null)
{
    global $wpdb;
    $keyword = $query->get('s');
    if ($keyword === '')
        return $sql;
    $keyword_wildcard = "{$keyword}*";
    $mode = "";
    if (strlen($keyword) <= 3) {
        $mode = " IN BOOLEAN MODE";
    }
    $sql = $sql . ", MATCH ({$wpdb->posts}.post_title, {$wpdb->posts}.post_content) AGAINST ('%s'{$mode}) AS relevance";
    return $wpdb->prepare($sql, $keyword_wildcard);
}

Rawposts-orderby-function.php

<?php
/**
 * Modify ORDER BY statement if orderby parameter is relevance
 *
 * @param string $sql
 * @param WP_Query $query
 *
 * @return string Modified query string
 */
function _my_posts_orderby_function ($sql, $query)
{
    if (!$query->get('s'))
        return $sql;

    $sql = " relevance {$query->query_vars['order']}";

    return $sql;
}

Rawposts-search-function.php

<?php
function _my_posts_search_function($sql, $query)
{
    global $wpdb;
    $keyword = $query->get('s');
    if (!$keyword || $sql == '') {
        return $sql;
    }
    
    $keyword_wildcard = "{$keyword}*";
    $mode = "";
    if (strlen($keyword) <= 3) {
        $mode = " IN BOOLEAN MODE";
    }
    $sql = " AND MATCH ({$wpdb->posts}.post_title, {$wpdb->posts}.post_content) AGAINST ('%s'{$mode}) ";
    return $wpdb->prepare($sql, $keyword_wildcard);
}

Rawwordpress-fulltext-search.sql

SELECT *, MATCH (post_title, post_content) AGAINST ('keyword' IN BOOLEAN MODE) AS relevance FROM wp_posts

SELECT *, MATCH (post_title, post_content) AGAINST ('keyword' IN BOOLEAN MODE) AS relevance FROM wp_posts

PreviousConfiguring Minimum and Maximum Word Length (ok)NextFullText cho cột post_tile, post_excertp, post_content và meta_value (oke)

Last updated