FULLTEXT Searching

[Ralph Guzman]

I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative.

I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query:

SELECT p2c.categories_id, p.products_id, pd.products_name,
p.products_quantity, p.products_image, p.products_bimage,
p.products_price, p.products_date_added, p.products_last_modified,
p.products_date_available, p.products_status
FROM products p, products_description pd, products_to_categories p2c
WHERE MATCH(p.products_model,pd.products_name,pd.product s_description)
AGAINST('pumps')
AND p.products_id = pd.products_id
AND p.products_id = p2c.products_id
ORDER BY pd.products_name;

This is how my table structures look:

CREATE TABLE `products` (
`products_id` int(11) NOT NULL auto_increment,
`products_quantity` int(4) NOT NULL default '0',
`products_model` varchar(12) default NULL,
`products_image` varchar(64) default 'image_na.gif',
`products_bimage` varchar(64) default 'image_na.jpg',
`products_price` decimal(15,4) NOT NULL default '0.0000',
`products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` decimal(5,2) NOT NULL default '0.00',
`products_status` tinyint(1) NOT NULL default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) default NULL,
`products_ordered` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added` (`products_date_added`),
FULLTEXT KEY `products_model` (`products_model`)
) TYPE=MyISAM AUTO_INCREMENT=928 ;

CREATE TABLE `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(64) NOT NULL default '',
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`),
FULLTEXT KEY `products_name_2`
(`products_name`,`products_description`)
) TYPE=MyISAM AUTO_INCREMENT=928 ;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw