Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Dec 2008
    Posts
    34
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question Basic Search Returns Results, but FULL-TEXT Doesn't?

    Hi,

    I'm new to FULL-TEXT searching, although I've been programing using MySQL & PHP for a number of years.

    I'm trying to do a search on a database, and doing a basic search returns a number of results:

    "SELECT * FROM table1 WHERE data like 'test'"

    this returns about 20 results, which includes records with both 'Test' & 'test'. However, doing a full-text search returns an empty set:

    "SELECT *, MATCH(data) AGAINST ('test') AS score FROM table1 WHERE MATCH(data) AGANIST ('test')"

    I have a similar query working that does return results on another table using:

    "SELECT ticket_id, MATCH(keywords,details) AGAINST ('test') AS score FROM table2 WHERE MATCH(keywords,details) AGANIST ('test')".

    I don't understand how the simple search is returning results, when the full-text doesn't. Am I formatting the query incorrectly?

    Many thanks

    Ben
    Last edited by bjblackmore; 02-18-2009 at 10:50 AM.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,858
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    The fulltext search ignores the results, if the number of records matched is more than 50% of the total number of records. Have a try by adding more records(with different value) in to your table.

    Quote Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html
    Every correct word in the collection and in the query is weighted according to its significance in the collection or query. Consequently, a word that is present in many documents has a lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Conversely, if the word is rare, it receives a higher weight. The weights of the words are combined to compute the relevance of the row.
    Last edited by abduraooft; 02-18-2009 at 12:01 PM.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    bjblackmore (02-18-2009)

  • #3
    New Coder
    Join Date
    Dec 2008
    Posts
    34
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question

    Thanks, that explains why it wasn't working. I've chaned the search to "IN BOOLEAN MODE" to ignore the threshold, which has improved the search, however, I notice on one of my tables one search returns lots of results, and another returns none. My records are:

    test_table
    |001|test|
    |002|test 1|
    |003|test 2|
    |004|test 3|
    |005|test 4|
    |006|test 5|
    |007|test 6|
    |008|test 7|
    |009|another test|
    |010|this is another test|

    If I search for 'test' it returns 10 results, if I search for another it returns zero results, even though test is 100% matched and 'another' only 20%.

    I have a production table (not a test), that contains support solution data such as 'applied hotfix 1', 'install SP2' etc. If I perform a search on this table, which currently has 30 records, of which only 2 are 'applied SP2' and I search for 'SP2', I get another empty set!

    Am I missing something?

    Thanks

    Ben

  • #4
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,858
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Your server's ft_min_word_len might be 4(which is the mysql default)
    See http://dev.mysql.com/doc/refman/5.1/...ne-tuning.html (If it's a shared server the possibility to alter those settings is very less)
    Last edited by abduraooft; 02-18-2009 at 02:14 PM.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    bjblackmore (02-18-2009)

  • #5
    New Coder
    Join Date
    Dec 2008
    Posts
    34
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Question

    MySQL is running on our own web server, so it will be possible to change, I just need to find out how to do that.
    From looking at http://dev.mysql.com/doc/refman/5.1/...ne-tuning.html am I right in thinking that I have to change the value for ft_min_word_len in the source, then recompile? Is there no way of editing an ini file, or setting it through the command shell?

    EDIT: OK, not to worry, managed to google a post and found you can edit my.ini adding 'ft_min_word_len=3' under: [mysqld] - *** MyISAM Specific options, then restarted the MySQL service & then running REPAIR TABLE tbl_name QUICK to re-index.
    Last edited by bjblackmore; 02-18-2009 at 02:23 PM.

  • #6
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,858
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Find out your my.cnf (mysql configuration) file and add
    ft_min_word_len = 3 under [mysqld] . Restart your server and re-index your table.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)


  •  

    Posting Permissions

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