SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Match against “ear ache” also returns stomach ache

    Can someone explain why ear ache in quotes allows rows to be returned that contain stomach ache? Shouldn’t the use of quotations return an exact match?

    MATCH (keyword) AGAINST ('“ear ache"' in boolean mode)


    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in boolean mode, the words inside quotes are searched for individually

    this is explained in the manual

    ear is only 3 letters, so it's treated as a stopword
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    That's why long ago I decided that mysq fulltext search is useless - stopwords which are ignored, if too many matches are found then none are returned, almost no customization, etc. I don't know who would like to use it? I suppose mysql.com uses fulltext search in their search engine - that would explain that I hardly ever can find anything using their search box in the documentation, now I don't even bother and use google to search mysql.com.

    In my sites I usually use a separate table where all data for searching are indexed and prepared in a proper form (eg. stripped tags, punctuation marks, accents on letters, etc.) and then I write code in my application that will convert the phrase to search into a series of LIKE and/or REGEXP expressions joined with AND or OR and this will give me enough room for customising how the search needs to function. Alas, I don't get the benefit of the speedy index but with a bit of caching mechanisms I can cope all right.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,081
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    I haven't tried it myself (yet), but you could try installing Spinx and search using that instead of with MySQL, which as others have pointed out isn't very good at searching. You can find a nice tutorial on Sphinx over here: http://www.ibm.com/developerworks/li...-sphinxsearch/
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

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

  5. #5
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If it's your mysql server you can change settings to allow 3 character words to work in fulltext. I did this on my internal mediawiki wiki and it definitely makes search more useful. I suspect doing this change on a heavily loaded busy server wouldn't be wise, though.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    DB-based fulltext search is limited and you typically need to do a bit more thinking than just calling the default fulltext functions.

    These days I would run with something lucene based if I needed search to work right. SOLR is a great option here -- it is lucene as a service with a HTTP API.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I used a shared server at my local ISP. Should I just ask them to make the change? Or would it be a waste of my time because they likely won’t do it?
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is doubtful they would make the change. You could ask.


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
  •