FULLTEXT should be working...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

FULLTEXT should be working...

Post 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?
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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/
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

Oooo.... nice bit of info there. Thanks AG.
Post Reply