Geek Answers Handbook

MySQL Full text search always has 0 results?

I read that using full-text search is faster than using LIKE %%. I updated my script, but always always has 0 results.

SELECT *, MATCH(pages) AGAINST('doodle') AS score FROM books WHERE MATCH(pages) AGAINST('doodle') ORDER BY score DESC

The $ keyword is longer than 4 characters and I index the page column as full text. I have a "doodle" in the page column in this "yankee doodle" format.

I also tried this

 SELECT *, MATCH(pages) AGAINST ('doodle' IN BOOLEAN MODE) AS score FROM books WHERE MATCH(pages) AGAINST ('doodle' IN BOOLEAN MODE)";

None of them work: \

+4
source share
1 answer

There are some bizarre quirks in full-text search.

For example, the behavior described in the last paragraphs of this page may be causing your problem:

.... for example, although the word "MySQL" is present on every line of the article table shown earlier, the search for the word does not produce results:

 mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST ('MySQL'); Empty set (0.00 sec)

The search result is empty because the word "MySQL" is present in at least 50% of the lines. Thus, it is effectively regarded as a stopwatch. For large data sets, this is the most desirable behavior: a natural language query should not return every second row from a 1 GB table. For small datasets, this may be less desirable.

The answer here will be to add a few lines or use a logical search.

If you need weighted search results, check out the comment Posted by John Craig on December 16 2009 7:01pm on the linked page for a workaround.

+13
source

More articles:


All Articles