Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: MATCH...AGAINST will match some words but not others...

    Hey everyone,

    I am having a little trouble with full text search within MySQL, whereby some words will return results, however others will not.

    Lets say I have the following table:

    Code:
    Row    | Title                        | Content
    ---------------------------------------------------------------------------------
    1        | Help topic 1              | Blah blah blahdity blah
    ---------------------------------------------------------------------------------
    2        | Another help topic      | Some other data here
    My query essentially looks like this:

    SELECT * FROM `table_name` WHERE (MATCH (Title) AGAINST ('search_text*' IN BOOLEAN MODE))

    The issue that I am having is that if enter 'topic' as my "search_text", both rows are returned, which is to be expected. If I use 'another' then an empty result is returned.

    I don't understand why this is, as it meets the min query length, and stop words should be ignored as I am using the IN BOOLEAN MODE...

    Could anyone shed any light as to why this is??

    Cheers

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps not enough rows in the table -- check the manual, it explains this issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    3
    Quote Originally Posted by r937 View Post
    perhaps not enough rows in the table -- check the manual, it explains this issue
    Hi r937,

    After some Googling, I could not find anything in the manual that mentions a minimum amount of rows for the query to return results. Could you please give me a point in the right direction?

    This seems peculiar behavior as a query should return any rows that match the criteria, regardless of how many rows actually exist within the table...

    Cheers

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by juzzbott View Post
    Could you please give me a point in the right direction?
    pointing ---> http://dev.mysql.com/doc/refman/5.0/...-language.html
    Quote Originally Posted by da manual
    For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results.

    If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    3
    Hi,

    Thanks for that information, I think the reason that I missed that information is because that relates to natural language queries. The last sentence of that information is: Users who need to bypass the 50% limitation can use the boolean search mode. As I am using boolean search mode, this behavior seems incorrect.

    Either way, I have now added some additional rows, with different data in the columns, and the result is still the same. Some words will return results, others (that are 4 or more characters in length) don't...

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by juzzbott View Post
    Thanks
    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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