MySQL full text fails on a particular table

Question

I've been tearing my hair as to why this fails I have the following code

$query = "
SELECT DISTINCT title, caption, message, url, MATCH(title, caption, message, url) AGAINST ('$searchstring' ) AS score FROM news WHERE (valid = 1) AND MATCH(title, caption, message, url) AGAINST ('$searchstring' ) UNION ALL  
SELECT DISTINCT title, caption, message, url, MATCH(title, caption, message, url) AGAINST ('$searchstring' ) AS score FROM paged WHERE (valid = 1) AND MATCH(title, caption, message, url) AGAINST ('$searchstring' )  ORDER BY score DESC";

I'm able to get search results from the paged table but not from the news table

Answer 1

My guess as to what the problem is: stopwords.

From the documentation:

The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match.

If paged didn't meet the criteria but news did then you'd get results for one and not the other.

Answer 2

Are both tables MyISAM? Or is news InnoDB?

Use this query to find out if you don't know.

select table_name
     , engine
  from information_schema.tables
 where table_name in('news','paged');

Because InnoDB type tables don't support fulltext.

Answer 3

GOT IT AT LAST, Thanks Guys.... HERE IS cause of the problem

If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones.

A natural language search interprets
the search string as a phrase in
natural human language (a phrase in
free text). There are no special
operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text
searches are natural language searches if the IN NATURAL LANGUAGE MODE
modifier is given or if no modifier is given.

one the one hand the table had only one entry thus the 50% benchmark was overshot even when I duplicated the entry 5 times the 50% benchmark was still an issue and relevance 0, so I added the modifier e.g. SELECT * FROM table WHERE MATCH(col1,col2) AGAINST('search_term' IN BOOLEAN MODE)

This is my first time posting on stackoverflow...didn't expect to get responses so fast,

Thanks