SitePoint Sponsor

User Tag List

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

    Not being able to search for three letter words

    I get complaints from my users that they can't search on three letter words like "eye". For years I was under the assumption that it's impossible with mySQL. Today I decided that I better ask the experts to see if I'm wrong. Here is the current query that I have in place. Please advise.

    Thanks!


    SELECT tID, date_format(date, '%m-%d-%Y') as date, viewed, summary, MATCH (summary,testimony,keywords) AGAINST ('$q') AS score FROM testimonies WHERE MATCH (summary,testimony,keywords) AGAINST ('$q') and approved = 'Yes' order by score desc limit 50
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    It certainly is possible with MySQL. There is an option ft_min_word_length you can put in the [mysqld] section of your my.ini and set it to the minimum length you want the full text search to be able handle (so in your case ft_min_word_length = 3).

    You can read all about it in da manual over here: http://dev.mysql.com/doc/refman/5.1/...ne-tuning.html
    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

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, it looks like these types of changes require a restart of the server. I pay a monthly hosting fee and so I don't have physical access to reboot the server on my own. What does someone in my situation do?

    Thank you!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    What does someone in my situation do?
    use LIKE instead of MATCH

    not quite as fast, but unless you have millions of testimonies, the difference shouldn't be noticeable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Rudy, do I need to have my ISP upgrade the mySQL version? Here is my query:

    SELECT tID, date_format(date, '%m-%d-%Y') as date, viewed, summary, LIKE (summary,testimony,keywords)
    AGAINST ('eye') AS score FROM testimonies WHERE LIKE (summary,testimony,keywords) AGAINST ('eye')
    and approved = 'Yes' order by score desc limit 50;

    Response:

    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'upgrade' at line 1

    Action:

    upgrade
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the error message doesn't match the query you posted, as it doesn't contain the word "upgrade"

    LIKE has different syntax from MATCH, it's effectively a rewrite
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, you're right. Here is the real one.

    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE' at line 1

    I am familiar with using like by itself, but maybe in a query like this, where multiple columns are being examined, I have the format wrong:

    SELECT tID, date_format(date, '%m-%d-%Y') as date, viewed, summary, LIKE (summary,testimony,keywords)
    AGAINST ('eye') AS score FROM testimonies WHERE LIKE (summary,testimony,keywords) AGAINST ('eye')
    and approved = 'Yes' order by score desc limit 50;
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    I am familiar with using like by itself...
    there is only one way to use LIKE
    Code:
    SELECT tID
         , DATE_FORMAT(date,'%m-%d-%Y') as date
         , viewed
         , summary
         , CASE WHEN summary   LIKE '%eye%' THEN 1 ELSE 0 END +
           CASE WHEN testimony LIKE '%eye%' THEN 1 ELSE 0 END +
           CASE WHEN keywords  LIKE '%eye%' THEN 1 ELSE 0 END AS score
      FROM testimonies 
     WHERE approved = 'Yes'
       AND ( summary   LIKE '%eye%'
          OR testimony LIKE '%eye%'
          OR keywords  LIKE '%eye%' )
    ORDER 
        BY score DESC LIMIT 50;
    you could run this whenever the user's search term is a single word of less than 4 cfharacters, and continue to use the MATCH query for other search terms
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    They don't mean to restart the physical server, just the mysqld daemon. If you have root access you can do that
    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

  10. #10
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That did it. Thanks again Rudy. Are you on elance.com by the way?
    Convert your dollars into silver coins. www.convert2silver.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    That did it. Thanks again Rudy. Are you on elance.com by the way?
    nope, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Maybe you should be. It would be an excellent way for you to make extra money with your powerful database skills.
    Convert your dollars into silver coins. www.convert2silver.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
  •