Re: #1191 - Can't find FULLTEXT index matching the column list

[John Hicks]

[EMAIL PROTECTED] wrote:
Ok. Got it.
And, since the able is already created and with tons of products inside,
how can I change the FULTEXT index?
I'm using phpMyAdmin.

Just create a new index. 
http://dev.mysql.com/doc/refman/4.1/en/create-index.html

--J

[EMAIL PROTECTED] wrote:
Hi to all!

I have "products" table:

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 need a single FULLTEXT index that contains both of the columns
you're searching on.

 From http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html :

For natural-language full-text searches, it is a requirement that the
columns named in the MATCH() function be the same columns included in
some FULLTEXT  index in your table. For the preceding query, note that
the columns named in the MATCH() function (title and body) are the same
as those named in the definition of the article table's FULLTEXT  index.
If you wanted to search the title or body separately, you would need to
create separate FULLTEXT indexes for each column.

--J





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]