Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    11,686

    Mysql gurus, a little help here please?

    So, I'm trying to find out why this isn't working here. One of the two below is, one is not:
    Code:
    SELECT * from videos WHERE title LIKE ('%%est%%') order by rating asc limit 0, 12
    Works


    Code:
    SELECT * from videos WHERE  MATCH (title,description,tags) AGAINST ('%%est%') order by rating asc limit 0, 12
    does not

    The index for the table:
    Code:
    ALTER TABLE videos ADD FULLTEXT(title, description, tags);
    Now I'm new to fulltext searches, but the idea is pretty simple, no?

    Select video from videos where EITHER title,description OR tags are matched against a phrase.

    The problem with #1 is that it is too limiting. IE:
    Indiana Jones and the Last Crusade would match the right video
    Indiana Jones the Last Crusade and would not , however

    It's my understanding that fulltext is supposed to compare the words (sans common words) separately, no? So, that's the route I want to take, as I understand it, but why isn't the query working right?
    WHMCS Guru - WHMCS addons, management, support and more.
    WHMCS Notifications Extended - Add slack, hipchat, SMS, pushover to WHMCS !!
    Always looking for Linux, WHMCS, Support Desk work. PM for details

  2. #2
    Join Date
    Aug 2002
    Location
    Canada
    Posts
    665
    Fulltext matches are a special creature. they use a significance hash, and not so much the words you have stored in a literal sense.

    You can check that the significance score is indeed the problem by trying:

    Code:
    SELECT * 
    FROM videos 
    WHERE MATCH (title,description,tags) AGAINST ('Indiana') > -1
    ORDER BY rating 
    LIMIT 0, 12
    Note that fulltext matches aren't like 'LIKE' matches where you need to specify wildcards. Fulltext matches require constant strings.

    So, moving forward, I think you are looking for boolean-mode fulltext matches which more closely replicate literal expansion matches (LIKE). Try this, see if it does what you like:

    Code:
    SELECT * 
    FROM videos 
    WHERE MATCH (title,description,tags) AGAINST ('Indiana' IN BOOLEAN MODE)
    ORDER BY rating 
    LIMIT 0, 12
    The significance score is a pita, especially in small databases. Words that exist in 50% or more of the result rows are considered common and do not match your queries, which I think may be what you are experiencing.

    Good luck.
    Alex
    Last edited by Saeven; 05-20-2008 at 01:23 PM.
    circlical - hosting software development
    forums * blog

Posting Permissions

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