Why MySQL Full text index doesn't work?

Question

After trying everything I could, I finally created this test table:

CREATE TABLE test_table (
  id int(11) NOT NULL AUTO_INCREMENT,
  title text NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY title (title)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

With the following test data:

INSERT INTO test_table (id, title) VALUES
(1, 'Evolving intelligence bayesian filtering power behind');

I would expect the following query to return 1 row:

SELECT * FROM test_table WHERE MATCH (title) AGAINST ('intelligence');

But it returns an empty set.

I have another database in the same mysql instance where full text searching works as expected. But for every new database I have created, full text doesn't work. I have rebuilt the indexes, repaired the tables, even inspected the index with myisam_ftdump. I'm out of ideas.

Do you have any idea about this problem? Thank you in advance.

Answer 1

Just for clarification. if your table does not contain enough data (e.g. 1 row) a fulltext search will not work. for words to be indexed they need to appear less common than in 50% of the rows.

So 'intelligence' occurs in 100% of the rows and is therefore not indexed.

Add at least 2 more rows without the word intelligence and it will start working. This is not made clear in the mySQL documentation and often catches newbies out. that's (me too) because we all test with small datasets.

DC