Purpose: The popular MySQL open source database suite is the back bone of a vast majority of LAMP type systems or rather system which requires web and database access. One such example is MediaWiki. Now MediaWiki has a very good default search engine including full-text search. However the other day, somebody informed me that they cannot find/locate Wiki articles using MediaWiki search that they had created. I was pretty stumped with this and I decided to give it a try myself and yes I found the same issues.

Problem Set-up

So before we start to look into the solution, it is necessary that we understand what is the actual problem. So let’s say you created a MediaWiki page with title “RJ-45 Ethernet”. Now let’s say some days later you would like to search for your article that you just created. So you go to your MediaWiki search box and type, “RJ-45″ and you find that it returns “zero” search results or rather you see the message “No page title matches”. You would think that there is something wrong with MediaWiki engine but the real issue is with MySQL configuration.

So let’s see how to resolve this…

Step 1: MySQL access

Make sure that you have “root” access to the MySQL database that is running on the Debian system which hosts your MediaWiki also.

Step 2: Check the value

MediaWiki’s full-text search features uses MySQL indexing capabilities. By default, MySQL indexes only “four or more” characters and this value is stored in a MySQL system variable called “ft_min_word_len”. So first check the value of this variable on your system:

# mysql -u root -p
Enter Password:

# mysql> SHOW VARIABLES like 'ft_min_word_len';
+-----------------+-----------+
| Variable_name   | Value |
+-----------------+-----------+
| ft_min_word_len | 4 |
+-----------------+-----------+
1 row in set (0.00 sec)

As you can the default value is “4″ and hence MediaWiki is unable to search for your article.

Step 3: Change the value

Edit the MySQL configuration file:

# nano /etc/mysql/my.cnf

and add the following line:

ft_min_word_len=3

in the following section:

[mysqld]

in the file

So basically after making the change, it should look something like this:

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
language    = /usr/share/mysql/english
skip-external-locking
ft_min_word_len=3

Save and quit the file.

Step 4: Restart your MySQL database

Now in order for the above modification to take effects, we need to restart your MySQL database:

# /etc/init.d/mysql restart

Step 5: Rebuild your index table

Next we need to rebuild the MediaWiki database index table.

From the MySQL prompt:

mysql> show databases;

and see the name of your MediaWiki MySQL database that you had created while installing MediaWiki. Let’s say the database name is “wikidb”.

mysql> use wikidb;
mysql> REPAIR TABLE searchindex QUICK;
mysql> quit

Step 6: Now search for the article again

Now go to MediaWiki search on the web-page and search for the same article by using the words “RJ-45″ and magically the search result will find your article which it did not before.

Step 7: Verify the changes (optional)

If you are still having trouble getting the search results verify that your changes (in Step 3) were in effect:

# mysql> SHOW VARIABLES like 'ft_min_word_len';
+-----------------+-----------+
| Variable_name   | Value |
+-----------------+-----------+
| ft_min_word_len | 3 |
+-----------------+-----------+
1 row in set (0.00 sec)

It should read whatever you set in Step 3.

That’s it.

Happy Searching!

Be Sociable, Share!