FullText unexpected results

juicy

I have a table consisting of rows guid, entrytime and body.
It represents a simple news article.
body is of type TEXT and is fulltext 'enabled'.

I have a few random entries to test the text searching, however I get retured strange results.

mysql> select guid, MATCH(body) against ('test') as score from fb.entry;
+------+-------+
| guid | score |
+------+-------+
| 1 | 0 |
| 2 | 0 |
| 5 | 0 |
+------+-------+
3 rows in set (0.01 sec)

Both records 1 and 2 have the substring 'test' in them, 5 does not.

I'm using mySQL 3.23.49 and the table is MyISAM. I'd expect different results. Any suggestions?

[deleted]

What you are actually seeing is a score of zero for all rows. The reason you are seeing this is for several reasons. I expect that you think you should be seeing the two rows returned that have 'test' in them. Let me show you why that won't happen the way you are doing it...

If a search term matches more than 50 percent of the table...no match is made.
you aren't limiting the table with a WHERE clause. You are returning all rows, but just selecting the score from the fulltext search as one of your return values.

Try this instead:

select guid, MATCH(body) AGAINST ('test') as score from fb.entry WHERE MATCH(body) AGAINST ('test')

with your current data in your table, you should have 0 rows returned because it matches more than 50 percent of the table. if you add a couple more rows in the table that do not have the word 'test' in them..then you should get the two rows returned, with the proper score...

Matt Wade
http://codewalkers.com/

juicy

I can't thank you enough. You were right about the lack of returned rows being because it matched > 50% of the total rows.

It's sorted now; so once again thankyou.