Hi
I was going through this tutorial about full text search.
I ran the following query and it worked:
SELECT entryID,title
FROM blog_entries
WHERE MATCH (title,entry) AGAINST('mother');
But when I ran the following, it didnot work:
SELECT entryID,title
FROM blog_entries
WHERE MATCH (title,entry) AGAINST('vacation');
Can someone please explain me why as the word “vacation” is already there in the “entry” column in entryID 3?
Many thanks in advance
how many rows are in your table?
4 rows
Assuming you used the exact data used in the example you’re pointing to, the problem seems to be that for this dataset 3 out of 4 rows contain the word “vacation”, which effectively makes it a stop word for MySQL and thus it ignores it. From http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html:
The search result is empty because the word “MySQL” is present in at least 50% of the rows. As such, it is effectively treated as a stopword. For large data sets, this is the most desirable behavior: A natural language query should not return every second row from a 1GB table. For small data sets, it may be less desirable.
You should either add more rows so the word “vacation” falls below 50% overall, or do the query in boolean mode.
SELECT
entryID
, title
FROM
blog_entries
WHERE
MATCH (title, entry)
AGAINST ('vacation' IN BOOLEAN MODE);
It would be more helpful if you’d post all the data for a more thorough inspection, i.e.
entryID posted categoryID title entry
------- ---------- ---------- ------------- ----------------------------------------------------------
1 1050942000 1 About miester I was born in michigan in 1980 in a small town called Adrian.
My mother is named Sue, while my father is named Mike.
They currently live in a small town called East Jordan. On April
27th, 2003 I will graduate from Eastern Michigan University with a
degree in Computer Information Systems.
2 1050942000 2 Today at work While I was at work today I was having some problems
with the RAID array. It seems that we have a rogue cron script that
is causing problems. When I find out more info I will post it here.
3 1050942000 1 Vacation! After I graduate I am taking a 2 week vacation. On my
agenda is a trip to Washington DC to see my girlfriend's sister
as well as throwing a few discs at the local disc golf course.
4 1050942000 1 Vacation! I have had a horrible cold for the last few days. Today I drank a
revive vitamin water with 150% of my daily dose of vitamin C. That
should help things out.
Just sayin’ 
Thanks for the reply. That helped me understand the actual issue.
Thanks again