Mysql – No results for match…against in boolean mode search with fulltext index (MySQL)

I have very bad news for you.

Unfortunately, that is the correct behavior.

The MySQL Query Optimizer tends to get sidetracked very easily when using a FULLTEXT index.

I also have very good news for you.

I wrote about this in StackOverflow on how to get around it.

You may have to nest your original query in a subquery and return the MATCH function as a column. Then, evaluate the MATCH column outside of the subquery.

Instead of your query

select * from seeds  WHERE  MATCH(text) AGAINST ("mount cameroon" IN BOOLEAN MODE) = 4;

you must refactor it into something like this:

SELECT B.* FROM
(
    SELECT id,MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE) score
    FROM seeds
    WHERE MATCH(text) AGAINST
    ("mount cameroon" IN BOOLEAN MODE)
) A
INNER JOIN seeds B USING (id)
WHERE A.score = 4;

Give it a Try !!!

Perhaps you could record the following from the three(3) tables

• table name
• the column from the table name
• FULLTEXT index on the column

Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;

Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;

Give it a Try !!!