#1
  1. Cliff Huxtable
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2004
    Location
    Hong Kong
    Posts
    1,174
    Rep Power
    145

    MySQL 5 dropping text search results under 3 characters


    Hi All,

    Thank you in advance for pondering my issue. Currently have a mySQL 5 installation on CentOS 5.0 and getting some weirdness with text search queries - very specific weirdness:

    let's say I've got the following table of data:
    Code:
    ID     fname      lname        content
    1      Greg        Stayl         blah blah blah
    2      Tim          Blahl         blah blah blah
    3      Bob         Smith        blah blah blah blah 
    4      Timo        Johns        Blah Blah
    A. If I run a search for "greg" it returns ID 1 - WORKING
    B. If I run a search for "gre" it returns ID 1 - WORKING
    C. If I run a search for "bob" it returns nothing - FAULTY
    D. If I run a search for "tim" it returns only ID 4 - FAULTY, should return ID 2 and ID 4.

    Here is my search query for Scenario D:
    Code:
     SELECT *, MATCH(`fname`,`lname`) AGAINST("bob*" IN BOOLEAN MODE) as `relavancy` FROM users WHERE MATCH(`fname`,`lname`) AGAINST("bob*" IN BOOLEAN MODE) ORDER BY relavancy
    For some reason, it isn't pulling the record matches with values under 4 characters. I've check my code and everything is working fine other than this. I would conjecture that it is a configuration issue with mySQL or simply, a bug. Perhaps my SQL syntax is somehow preventing 2 and 3 character exact matches.

    Any help would be appreciated in resolving this issue.

    Thanks,

    Taylor
    Pop, OJ, Purple Stuff, and ... Sunny D! Wow, dude -- Your Mom is the coolest! Thanks Mrs. D!
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    There's a limit by design, but it can be changed, see here

    Comments on this post

    • medianox agrees : Came back here to thank you for this answer. I rqrd the link yet again. Cheers.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    fulltext searching seems inappropriate for firstname/lastname columns

    any reason you're not using LIKE instead?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Cliff Huxtable
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2004
    Location
    Hong Kong
    Posts
    1,174
    Rep Power
    145
    Originally Posted by r937
    fulltext searching seems inappropriate for firstname/lastname columns

    any reason you're not using LIKE instead?
    Good point; however, I'm just providing an example above so that the question made sense. If you could see all the fields i'm searching through, you would see that a full text search makes sense.

    Thanks for the input.
    Pop, OJ, Purple Stuff, and ... Sunny D! Wow, dude -- Your Mom is the coolest! Thanks Mrs. D!
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    $expletive!!!

    pablo, how many times have i fallen for this stunt

    i think i'll take a holiday from answering questions

    this is really starting to annoy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    Originally Posted by r937
    $expletive!!!

    pablo, how many times have i fallen for this stunt

    i think i'll take a holiday from answering questions

    this is really starting to annoy
    Yes, looks like working in a call center ...

IMN logo majestic logo threadwatch logo seochat tools logo