Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    96

    Unanswered: FULLTEXT seach returns an empty set

    Hello,
    i am trying to do a full text search in MySQl using the following SQL:
    SELECT prod_desc FROM tblproducts WHERE MATCH (prod_desc) AGAINST ('is')

    The database is returning an empty set where "is" is to be found is several
    rows.
    The prod_desc field is a text set as FULLTEXT index

    Any help would be appreciated.
    PS: using this technique could I search 2 columns in the same table with one
    select query?
    Regards,

    Oli

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    I think the minimum you can search for is strings with three characters (might be four but certainly two isn't allowed).

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    I would have said that 'is' is a stopword, that is a common word that is not indexed by full text indexes (there would be too many, these words are not significant and would make the indexes VERY BIG). I still think 'is' is indeed one of such words, but moreover, guelphdad is right : a search cannot be performed on words shorter than 4 characters.

    From the MySQL Manual :
    MySQL uses a very simple parser to split text into words. A ``word'' is any sequence of characters consisting of letters, digits, `'', or `_'. Some words are ignored in full-text searches:

    * Any word that is too short is ignored. The default minimum length of words that will be found by full-text searches is four characters.
    * Words in the stopword list are ignored. A stopword is a word such as ``the'' or ``some'' that is so common that it is considered to have zero semantic value. There is a built-in stopword list.
    I think 'is' falls in both categories.

    Regards,

    RBARAER

  4. #4
    Join Date
    Aug 2004
    Posts
    96
    Thanks guys,
    yse is was 2 short and I needed 4 characters minimum.
    Now I am trying to search 2 columns at the same time, it is working fine with both columns separatly but as soon as i had the 2 i get an error message:

    [MySQL][ODBC 3.51 Driver][mysqld-4.0.15-nt]Can't find FULLTEXT index matching the column list
    /sgallery/shop/search_result.asp, line 11


    Here is the SQl I am using:

    SELECT * FROM tblproducts WHERE MATCH (prod_name,prod_desc) AGAINST ('keyword')

    Thanking you in advance

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    You are getting this error because you created your FULLTEXT index only on one column (prod_desc). If you want to search prod_name and prod_desc in one shot, you have to create a FULLTEXT index on (prod_name, prod_desc). According to the MySQL manual, you can use MATCH on any column included in a FULLTEXT index, which means that in your case, if you create just a FULLTEXT index on (prod_name, prod_desc), you will be able to use MATCH(prod_name, prod_desc), MATCH(prod_name) and MATCH(prod_desc).

    Regards,

    RBARAER

Posting Permissions

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