Skip to main content
Added myisam tag since the table with the fulltext index is MyISAM
Link
RolandoMySQLDBA
  • 184.4k
  • 33
  • 323
  • 531
Tweeted twitter.com/#!/StackDBAs/status/152530886498328576
Source Link
Abdul Manaf
  • 9.7k
  • 16
  • 73
  • 84

MySQL Full Text index issue

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)