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.products_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/[EMAIL PROTECTED]