MySQL fulltext search does not work

Question

I have a MySQL table structured like this:

$sql = "CREATE TABLE test(
id INT NOT NULL,
title VARCHAR(512) NOT NULL COLLATE utf8_general_ci,
body TEXT NOT NULL COLLATE utf8_general_ci,
dateStored VARCHAR(32) NOT NULL COLLATE utf8_general_ci,
fileName VARCHAR(128) NOT NULL COLLATE utf8_general_ci,

FOREIGN KEY(id) REFERENCES `database`.`parent`(id)

);";

My storage engine is MYISAM.

Now, when I want to search in body field with the MATCH - AGAINST, there is no result…

This is my try:

$sql2 = "SELECT * FROM database.test 
        WHERE MATCH(body) AGAINST('?')";
$prepare = $pdo->prepare($sql2);
$prepare->execute( array( $pattern ) );
$fetch = $prepare->fetchAll();

It seems very basic but still my code do not work. Would you tell me what is wrong with my code?

Is this because of I have a field with references to another table?

Answer 1

Add more data. By default MySQL will ignore any word that is in 50% or more of the rows in the table as it considers it would be a 'noise' word.

With very few rows in a table, it is common to hit this 50% limit often (ie. if you have two rows, every word is in at least 50% of the rows!).

Answer 2

I think you are missing the FULLTEXT index on your field. You need to have a FULLTEXT index on a column to do MATCH AGAINST searches.

Either declare the FULLTEXT index with the table or alter your current table to include the index.

ALTER TABLE `test` ADD FULLTEXT (`body`)

Also, as some others have said, there is a cut off to the word length and there is also the 50% threshold that will prevent results from showing.