SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL Full text search

    Hi

    I was going through this tutorial about full text search.

    I ran the following query and it worked:

    Code:
    SELECT entryID,title
    FROM blog_entries
    WHERE MATCH (title,entry) AGAINST('mother');
    But when I ran the following, it didnot work:

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    how many rows are in your table?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    4 rows

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    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.

    Code sql:
    SELECT
       entryID
     , title
    FROM
       blog_entries
    WHERE
       MATCH (title, entry)
       AGAINST ('vacation' IN BOOLEAN MODE);
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    4 rows
    It would be more helpful if you'd post all the data for a more thorough inspection, i.e.

    Code:
    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'
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    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
    Last edited by ScallioXTX; Aug 11, 2012 at 05:35. Reason: fixed quote


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •