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

    Exclamation Unanswered: Full text search does not return expected result

    Hello,
    I have a full text search on a table and a FULLTEX index called searchIndex on 3 colums, prod_name, prod_shortdescription, prod_description

    When searching the db if the keyword is found in the first field prod_name, the record or records are returned fine, however if the keyword is found in any other fields an empty string is returned, not even the records previously returned are found.
    here is the SQL syntax I am using:

    Code:
    SELECT tblproducts.prodId, tblproducts.prod_name, tblproducts.prod_shortdesc, tblproducts.prod_price, tblproducts.taxId,tblproducts.prod_image, tblproducts.prod_status, tbltax.taxRate, tblproducts.prod_discslogan FROM tblproducts, tbltax WHERE MATCH (prod_name, prod_shortdesc, prod_desc) AGAINST ('%keyword%')
    Would any one know why this is happening.

    Also, any keyword entered must be at least 5 character in length to be considered by default, is it possible to change this, I am working on a shop selling fishing gear meaning than someone searching for rod will see no return whereas there will be 50 rods in the catalogue .

    Thanking you in advance
    Olivier

  2. #2
    Join Date
    Aug 2003
    Location
    Reston, VA
    Posts
    59

    Post the index..

    Could you post the details of the index? I assume you are using MyISAM tables?? The 5 character workaroudn is somewhere in the configuration file I believe...it usually defaults to 3 characters if I"m not mistaken, but I need to get it down to 2 characters myself.

    Not to nit-pick, but are 'prod_shortdescription' and 'prod_description' the same as 'prod_shortdesc' and 'prod_desc'. I ask because I make typos all the time and I think it would explain the behavior (or not...I think it would produce an error).

    good luck...i'll try to post later about the character limitation.

  3. #3
    Join Date
    Aug 2004
    Posts
    96
    Hello,
    yes you are right, it is a typo

    Here is what I have no the table:

    CREATE TABLE `tblproducts` (
    `prodId` varchar(30) NOT NULL default '',
    `prod_name` varchar(100) NOT NULL default '',
    `prod_shortdesc` tinytext NOT NULL,
    `prod_desc` mediumtext NOT NULL,
    `catId` int(3) NOT NULL default '0',
    `subcatId` text,
    `cat2` int(3) NOT NULL default '0',
    `subcat2` text NOT NULL,
    `cat3` int(11) NOT NULL default '0',
    `subcat3` text NOT NULL,
    `use_size` tinyint(1) NOT NULL default '0',
    `sizeId` varchar(250) default '0',
    `use_color` tinyint(1) NOT NULL default '0',
    `colorId` varchar(250) default '0',
    `prod_image` varchar(50) default '',
    `prod_image2` varchar(50) NOT NULL default '',
    `prod_image3` varchar(50) NOT NULL default '',
    `prod_image4` varchar(50) NOT NULL default '',
    `mini_order` int(5) NOT NULL default '1',
    `taxId` int(3) NOT NULL default '0',
    `prod_price` decimal(10,6) NOT NULL default '0.000000',
    `use_inventory` tinyint(1) NOT NULL default '0',
    `inv_number` int(11) NOT NULL default '0',
    `inv_alert` int(11) NOT NULL default '0',
    `inv_options` tinyint(1) NOT NULL default '0',
    `prod_discPrice` decimal(10,2) default '0.00',
    `prod_discSlogan` varchar(70) default '',
    `prod_status` int(1) NOT NULL default '0',
    PRIMARY KEY (`prodId`),
    FULLTEXT KEY `searchIndex` (`prod_name`,`prod_shortdesc`,`prod_desc`)
    ) TYPE=MyISAM;


    Thanking you for your help

  4. #4
    Join Date
    Aug 2003
    Location
    Reston, VA
    Posts
    59

    all the time

    it happens to me all the time and i suspect many of the people here. after looking at something too long...it all looks the same.

  5. #5
    Join Date
    Aug 2004
    Posts
    96
    Hello,
    the typo was only on the post, I am still having the same problem with MySQl
    Regards,
    Olivier

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

    First of all, looking at your query, I think you miss a join between your two tables tblproducts and tbltax, don't you ?

    Now, why do you put '%' before and after keyword ? AFAIK, it will search for all words containing your keyword (at the beginning, in the middle or at the end), which should be much slower than searching the entire word directly.

    Furthermore, I think your problem comes from here :
    mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('MySQL');
    Empty set (0.00 sec)

    The search result is empty because the word ``MySQL'' is present in at least 50% of the rows. As such, it is effectively treated as a stopword.
    The word you're looking for must be present in too many rows. Maybe the '%' you added make this even worse. If you want to know more, see the MySQL Manual.

    You will also find more about the minimum length for a word to be looked for :
    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.
    Hope that helps,

    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
  •