Removing default search limitations in MediaWiki and MySQL


Introduction

So let’s assume you have created a page in your Mediawiki site, named it with an appropriate page title consisting of only 3 characters, say SSL or SQL, only to find that it never shows up when searching for it in the MediaWiki site search. You have tried both uppercase and lowercase search query words, you haven’t misspelled the search query (only 3 characters, after all), and you are certain you are searching through the correct MediaWiki namespace. You are also certain that you saved the page, not only previewed it and then made the mistake of closing your browser.

Come to think of it, that page you are 110% certain you read yesterday, referring to the exact same search query word, say ‘SSL’, doesn’t show up either when searching for either of ‘ssl’ or ‘SSL’. How can that be? You didn’t delete the page by mistake, did you? This is getting annoying, but are you starting to see a pattern?

As we will see this has nothing to do with searching the wrong MediaWiki namespace (although easy enough to do), misspelling the search query word, distinguishing between uppercase or lowercase search query words, failing to save a page edit or deleting a page by mistake. The cause is simple: by default, the MySQL database server does not fulltext index words of 3 characters or below. To make this possible, you will have to change the configuration of both MediaWiki and MySQL to enable it to search for useful words and abbreviations of character length 3, such as apt, ssh, nfs and the like.

Editing the MediaWiki configuration

It is simple to correct this problem, but you need access to both the web server file system and administrator privileges to the MySQL database server. Locate the LocalSettings.php file in your MediaWiki installation directory, usually a subdirectory of your web server’s document root, say /htdocs on Apache. Edit the file and add the variable wgDBminWordLen to it. Set it’s value to 3, so:

$wgDBminWordLen = 3;

Editing the MySQL configuration

You must also tell the MySQL database server to index 3-letter words in fulltext indexes. This is the core of the problem and this configuration change will also apply to any application using the MySQL database server, not just MediaWiki. Add or edit the [mysqld] heading in your my.conf file with the following configuration line:

[mysqld]
ft_min_word_len=3

And that’s really all there is to it!

Recreate the MySQL table index

So just one more step to go before you can use it. You now need to restart your MySQL database server to load the new configuration setting, and then recreate the search index. So restart your server process, open a console and proceed to enter the MySQL database you use for your MediaWiki installation. Recreate the necessary search index by typing:

REPAIR TABLE searchindex QUICK;

If you prefixed your MediaWiki database tables with a prefix when installing the wiki then the name of your ‘searchindex’ table may be wk_searchindex or similar. A simple ‘show tables;’ query will give you a hint of the correct table name.

Summary

This particular fix took me a little while to find. Not that it was hidden, but I was unsure which component was limiting the search. As you have seen it turns out both MediaWiki and MySQL needed to be tweaked to correct the problem. Remember there is a reason why MySQL sets the index limitation to 3 characters by default. By changing it you will increase your index sizes leading to a performance hit. The default setting is 4 characters and I’m sure there is a statistical reason for this. Take a closer look at this page for more information on tuning the fulltext search in MySQL if you are unsure of the impact it might have.

Advertisements

6 thoughts on “Removing default search limitations in MediaWiki and MySQL

  1. Thank you so much for this. It took me some time to first understand that the problem was that the indexing started from 4 letters, googling how to fix it.

  2. Pingback: Searching the forums for ABF - Ingres Community Forums

  3. Hm,
    it looks like I did something wrong.

    Here is a snipped of what I did:

    wiki:/usr/share/mediawiki# grep wgDBminWordLen LocalSettings.php
    $wgDBminWordLen = “3”;
    wiki:/usr/share/mediawiki# grep min /etc/mysql/my.cnf
    ft_min_word_len=3
    wiki:/usr/share/mediawiki# echo ‘REPAIR TABLE searchindex QUICK’ | mysql -ppassword wikidb
    Table Op Msg_type Msg_text
    wikidb.searchindex repair status OK

    wiki:/etc/mysql# /etc/init.d/mysql restart
    Stopping MySQL database server: mysqld

    .
    Starting MySQL database server: mysqld.
    Checking for corrupt, not cleanly closed and upgrade needing tables..

    Any ideas?

    Thanks in advance!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s