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:

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

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.

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

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.

Hello,
the typo was only on the post, I am still having the same problem with MySQl
Regards,
Olivier

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 :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 :
Hope that helps,

Regards,

RBARAER