MySQL Full text search

[cancer10]

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

[r937]

how many rows are in your table?

[cancer10]

4 rows

[ScallioXTX]

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/...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);

[ScallioXTX]

4 rows

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'

[cancer10]

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/...language.html: [...]

Thanks for the reply. That helped me understand the actual issue.


Thanks again