Page 1 of 1

FULLTEXT should be working...

Posted: Mon Jan 24, 2005 2:57 am
by Steveo31
Table Structure:

Code: Select all

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `product_id` varchar(100) NOT NULL default '',
  `username` varchar(30) NOT NULL default '',
  `product_name` varchar(30) NOT NULL default '',
  `quantity` smallint(5) unsigned NOT NULL default '0',
  `description` text NOT NULL,
  `keywords` varchar(255) NOT NULL default '',
  `special` text NOT NULL,
  `main_category` varchar(35) NOT NULL default '',
  `sub_category` varchar(35) NOT NULL default '',
  `price` int(11) NOT NULL default '0',
  `hits` int(10) unsigned NOT NULL default '0',
  `search_hits` int(10) unsigned NOT NULL default '0',
  `store_name` varchar(40) default NULL,
  `store_owner` varchar(30) default NULL,
  `store_email` varchar(50) default NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`,`store_name`),
  FULLTEXT KEY `product_name` (`product_name`),
  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `keywords` (`keywords`),
  FULLTEXT KEY `product_name_2` (`product_name`,`description`,`keywords`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
Now, there is the product_name_2 in there, I can only guess because there is already one for product_name defined. In "MySQL" by DuBois, it says that there should be one for each column you want to search, plus one for all in case I am going to search one or the other sometimes, and all of em sometimes.

Code: Select all

SELECT * FROM products;
Regardless of all the other stuff, the column description comes back with "This is a really great product for you to buy. It does a lot of things you think are not as cool as you think."

Code: Select all

SELECT * FROM products WHERE MATCH(description) AGAINST('product')
Should come back with something, comes back with nothing. :(

Any ideas to why?

Posted: Mon Jan 24, 2005 4:42 am
by AGISB
The 50% Threshold
MySQL removes noise words and short words, but if a word is present in more than 50% of the records being searched, then those records will not be returned. MySQL calls this the "50% threshold". In a way this makes sense, as it filters out records that have a low relevance.
Taken from http://www.devarticles.com/c/a/MySQL/Ge ... ilities/3/

Posted: Tue Jan 25, 2005 9:03 pm
by Steveo31
Oooo.... nice bit of info there. Thanks AG.