phantom007 — 2012-08-11T06:34:47-04:00 — #1
I was going through this tutorial about full text search.
I ran the following query and it worked:
WHERE MATCH (title,entry) AGAINST('mother');
But when I ran the following, it didnot work:
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
r937 — 2012-08-11T07:45:59-04:00 — #2
how many rows are in your table?
phantom007 — 2012-08-11T08:01:49-04:00 — #3
scallioxtx — 2012-08-11T08:17:18-04:00 — #4
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.
MATCH (title, entry)
AGAINST ('vacation' IN BOOLEAN MODE);
scallioxtx — 2012-08-11T08:18:53-04:00 — #5
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.
phantom007 — 2012-08-11T08:33:25-04:00 — #6
Thanks for the reply. That helped me understand the actual issue.