SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    FULLTEXT search fails on "ac/dc"

    Anyone have a clue why FULLTEXT search would fail on a term like "ac/dc"?

    Code MySQL:
    select id, title from table where match(title) against("ac/dc" in boolean mode)

    I am aware the words with less than 4 characters are ignored, but this should work no?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    fail?

    was there an error message?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I meant it returns no rows, doesn't find anything.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    how many total rows in the table? and how many contain this string when you use LIKE to find them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    total rows about 8, 2 rows contain ac/dc.

    Sorry, not in front of the other computer right now.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    from da manual...
    [fulltext searching] works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so I guess in proper English, this translates to something like "my table doesn't have enough rows". I now have 21 entries with the same result. I doubt that constitutes a "very small table".

    Furthermore, other fulltext searches work fine (searches for "school" or "school of rock", etc), just not with ac/dc so I doubt that this is what's causing it. Of course I could be wrong.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes that would still constitute a very small table.

    I believe the problem is the / which MAY be interpreted as you now searching on two two letter words "ac" and "dc" and those would be stop words because the minimum word length is four characters. I believe you can change that setting. I do think though this is why you run into the trouble.

    Change a couple of rows to acdc without the / and search on that and I'll bet the rows are returned.

  9. #9
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, this was my same conclusion, but then how do I deal with terms that have a slash in them?

    Because the same search using LIKE does work.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  10. #10
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Did you try using single quotes around 'ac/dc' ?
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  11. #11
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I did. Single, double, same thing. It's really got me stumped!
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  12. #12
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think what is happening is that FULLTEXT is dividing the term into 2 words because of the slash and of course it will ignore words of 3 characters or less. The question is, is there a way to tell it not to divide words when there is a slash present, it does the same thing for a hyphen.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  13. #13
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone have any clues as to why this is not working for fulltext search and how to circumvent it?

    Any help would be appreciated.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com


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
  •