MySQL Full Text index issue

Question

I Have a table articles with following data

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  7 | ABCD                  | Following things are not upto date       |
|  8 | RockOn                | is the Following                         |
+----+-----------------------+------------------------------------------+
8 rows in set (0.00 sec)

when i write the query as..

mysql> select * from articles where match(`body`) against('database');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

the result is as expected.

but when i issue the query as

mysql >select * from articles where match(`body`) against('following' in boolean mode);
Empty set (0.00 sec)

why it shows empty set as there is record corresponding to the query.

I have Full text index on body.

mysql> show create table articles\G
*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `body` (`body`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Answer 1

It probably seems that following is in the stop words list.
you should check the stop words to see is that the case.

Answer 2

@John.Locke's answer hits the nail on the head. +1 on your answer.

@DTest's comment confirmed it. +1 on your comment.

Here is how to get around it:

Step 01) Create an empty stopword list

$ echo -n > /var/lib/mysql/stopwords.txt

Optional : Make the stopword list have articles (no pun intended) 'a','an','the'

$ echo "a"    > /var/lib/mysql/stopwords.txt
$ echo "an"  >> /var/lib/mysql/stopwords.txt
$ echo "the" >> /var/lib/mysql/stopwords.txt

Step 02) Configure mysql to accept everything in FULLTEXT indexes minus your custom stopwords

[mysqld]
ft_min_word_len=1
ft_stopword_file=/var/lib/mysql/stopwords.txt

Step 03) Restart mysql

$ service mysql restart

Step 04) Reindex the table

mysql> ALTER TABLE articles DROP INDEX body;
mysql> ALTER TABLE articles ADD FULLTEXT (body);

Give it a Try !!!

CAVEAT

Anytime you reindex a FULLTEXT index after customzing to allow more words to be indexed, that resulting index with definitely be bigger.