Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
1  
    
@DTest : Bingo. following is in the standard stopword list. –  RolandoMySQLDBA Dec 29 '11 at 16:46
add comment

@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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.