CODEWITHSUNDEEP
phpmysqlsearch
user3665682
user3665682

Reputation: 21

MySQL fulltext search does not work

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?

Upvotes: 2

Views: 2078

Answers (4)

automatix
automatix

Reputation: 14522

For MySQL versions 5.6+

You just need to add a FULLTEXT INDEX altering the table like Crackertastic described or define it directly in the CREATE TABLE statement.

Here is a working example:

-- DROP TABLE child;
-- DROP TABLE parent;

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB; -- or MyISAM
CREATE TABLE child(
    id INT NOT NULL,
    title VARCHAR(512) NOT NULL COLLATE utf8_general_ci,
    body TEXT NOT NULL COLLATE utf8_general_ci,
    date_stored VARCHAR(32) NOT NULL COLLATE utf8_general_ci,
    file_name VARCHAR(128) NOT NULL COLLATE utf8_general_ci,
    parent_id INT NOT NULL,

    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES parent(id),
    FULLTEXT (body)
) ENGINE=InnoDB; -- or MyISAM

INSERT INTO child (id, title, body, date_stored, file_name, parent_id) VALUES('1','test title 1','test body 1','22.05.2014','file1.txt',5);
INSERT INTO child (id, title, body, date_stored, file_name, parent_id) VALUES('2','asd','qwer','31.07.2019','yxcv.txt',6);
INSERT INTO child (id, title, body, date_stored, file_name, parent_id) VALUES('3','gfdsa','trewq','21.04.2015','hjkl.txt',7);

SELECT
    *
FROM
    child
WHERE
    MATCH(body) AGAINST('qwer')
;

The SELECT request returns the entry with the id = 2.

For older MySQL versions

In MySQL vesions older than 5.6 was not possible to define a FULLTEXT INDEX on an InnoDB table.

From the MySQL 5.5 docu:

Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.)

So, one had to decide between using of FOREIGN KEY and FULLTEXT INDEX and choose the storage engine -- InnoDB or MyISAM. Since InnoDB supported FOREIGN KEY constraints, but didn't support FULLTEXT indexes.

An attempt to create a FULLTEXT INDEX on an InnoDB table

-- DROP TABLE child;
-- DROP TABLE parent;

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE child(
    id INT NOT NULL,
    title VARCHAR(512) NOT NULL COLLATE utf8_general_ci,
    body TEXT NOT NULL COLLATE utf8_general_ci,
    date_stored VARCHAR(32) NOT NULL COLLATE utf8_general_ci,
    file_name VARCHAR(128) NOT NULL COLLATE utf8_general_ci,
    parent_id INT NOT NULL,

    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=InnoDB;

ALTER TABLE `child` ADD FULLTEXT (`body`);

failed with an error:

Query: ALTER TABLE `child` ADD FULLTEXT (`body`)

Error Code: 1214
The used table type doesn't support FULLTEXT indexes

So, you had to use MyISAM for FULLTEXT INDEX. Then you just need to add a FULLTEXT INDEX altering the table like Crackertastic described or define it directly in the CREATE TABLE statement.

Here is a working example:

-- DROP TABLE child;
-- DROP TABLE parent;

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM;
CREATE TABLE child(
    id INT NOT NULL,
    title VARCHAR(512) NOT NULL COLLATE utf8_general_ci,
    body TEXT NOT NULL COLLATE utf8_general_ci,
    date_stored VARCHAR(32) NOT NULL COLLATE utf8_general_ci,
    file_name VARCHAR(128) NOT NULL COLLATE utf8_general_ci,
    parent_id INT NOT NULL,

    PRIMARY KEY (id),
    FULLTEXT (body)
) ENGINE=MyISAM;

INSERT INTO child (id, title, body, date_stored, file_name, parent_id) VALUES('1','test title 1','test body 1','22.05.2014','file1.txt',5);
INSERT INTO child (id, title, body, date_stored, file_name, parent_id) VALUES('2','asd','qwer','31.07.2019','yxcv.txt',6);
INSERT INTO child (id, title, body, date_stored, file_name, parent_id) VALUES('3','gfdsa','trewq','21.04.2015','hjkl.txt',7);

SELECT
    *
FROM
    child
WHERE
    MATCH(body) AGAINST('qwer')
;

The SELECT request returns the entry with the id = 2.

Upvotes: 1

Jeff
Jeff

Reputation: 167

Try IN BOOLEAN MODE. This will not ignore any rows.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

Upvotes: 1

Crackertastic
Crackertastic

Reputation: 4913

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.

Upvotes: 3

Karim Baidar
Karim Baidar

Reputation: 559

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!).

Upvotes: 5

Related Questions