See response below:
On 5/9/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
It looks like today is my day! :)
I FULLTEXT indexed my table "products":
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '0000-00-00',
`prod_end_date` date default '0000-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_name` (`prod_name`),
FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;
When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list
What am I doing wrong?
You put 2 FULLTEXT indexes on different columns, and you're trying to
match against one multi-column index, which doesn't exist. Your table
creation allows:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
SELECT * FROM products
WHERE match (prod_description) against ('+red +shirt');
or
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt')
OR match (prod_description) against ('+red +shirt');
If you want to allow the query you originally wrote, you should have
one multi-column FULLTEXT index, like so:
FULLTEXT KEY `keyname` (`prod_name`,`prod_description`)
But I'm guessing what you want is to change your query -- which allows
you to match on either the name OR the description.
-Sheeri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
