Can't find FULLTEXT index matching the column list

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
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Can't find FULLTEXT index matching the column list

Post by mikebr »

Pimptastic | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I'm a bit puzzeled on with the following, this works on my local machine but on the server I get a "Can't find FULLTEXT index matching the column list" error!

Code: Select all

SELECT `i_id` FROM `images`  WHERE  `i_location` = 'Malaga' AND `i_cat` = 'roads'  AND MATCH( `i_kwords`, `i_comments` ) AGAINST( 'mountain' )
This following query is searching in one table, and it's fine, no errors:

Code: Select all

SELECT * FROM `images` WHERE `i_location` = 'Malaga' AND `i_cat` = 'roads' AND MATCH( `i_kwords` ) AGAINST( 'mountain' )
Strange it works on my local "OSX" machine but when it's uploaded to the "I think linux" server I get errors.

Anyone any ideas?
Thanks


Pimptastic | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Is "i_comments" definately set as a FULLTEXT index on your server database?
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Yes, the index is as follows:

Keyname Type Cardinality Field

PRIMARY PRIMARY 2517 i_id
i_location FULLTEXT None i_location
i_comments FULLTEXT None i_comments 1
i_kwords FULLTEXT None i_kwords 1

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the indexes are separate from each other, you'll need to make separate match() statements for each, I believe.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

I have just added the following index to the indexes:

Keyname Type Cardinality Field
i_kwords_2 FULLTEXT None i_kwords 1 i_comments 1

but I still get the error: Can't find FULLTEXT index matching the column list

surly I don't need to add 'i_location' & 'i_cat' to the index?
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

I just created another index including all the other columns passed in the query and it makes no difference, so I have answered my last question, this makes no difference!

Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post the create table of this and the query or queries you're trying to use now.

Code: Select all

SHOW CREATE TABLE `table`
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

CREATE TABLE

Code: Select all

CREATE TABLE `_images` (
 `i_id` int(11) NOT NULL auto_increment,
 `i_name` varchar(60) NOT NULL default '',
 `i_cat` varchar(15) NOT NULL default '',
 `i_location` varchar(30) NOT NULL default '',
 `i_kwords` text,
 `i_comments` text,
 `i_imagedate` varchar(18) NOT NULL default '||',
 `i_timestamp` int(11) unsigned NOT NULL default '0',
 PRIMARY KEY  (`i_id`),
 FULLTEXT KEY `i_location` (`i_location`),
 FULLTEXT KEY `i_comments` (`i_comments`),
 FULLTEXT KEY `i_kwords` (`i_kwords`),
 KEY `i_kwords_2` (`i_kwords`(1),`i_comments`(1))
) TYPE=MyISAM
The query that's causing the error:

Code: Select all

SELECT `i_id` FROM `images`  WHERE  `i_location` = 'Malaga' AND `i_cat` = 'roads'  AND MATCH( `i_kwords`, `i_comments` ) AGAINST( 'mountain' )
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

mysql> CREATE TABLE `_images` (
    ->  `i_id` int(11) NOT NULL auto_increment,
    ->  `i_name` varchar(60) NOT NULL default '',
    ->  `i_cat` varchar(15) NOT NULL default '',
    ->  `i_location` varchar(30) NOT NULL default '',
    ->  `i_kwords` text,
    ->  `i_comments` text,
    ->  `i_imagedate` varchar(18) NOT NULL default '||',
    ->  `i_timestamp` int(11) unsigned NOT NULL default '0',
    ->  PRIMARY KEY  (`i_id`),
    ->  FULLTEXT KEY `i_location` (`i_location`),
    ->  FULLTEXT KEY `i_comments` (`i_comments`),
    ->  FULLTEXT KEY `i_kwords` (`i_kwords`),
    ->  KEY `i_kwords_2` (`i_kwords`(1),`i_comments`(1))
    -> ) TYPE=MyISAM ;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> SELECT `i_id` FROM `_images`  WHERE  `i_location` = 'Malaga' AND `i_cat` = 'roads'  AND MATCH( `i_kwords`, `i_comments` ) AGAINST( 'mountain' );
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
mysql> ALTER TABLE `_images` ADD FULLTEXT `foo` (`i_kwords`, `i_comments`);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT `i_id` FROM `_images`  WHERE  `i_location` = 'Malaga' AND `i_cat` = 'roads'  AND MATCH( `i_kwords`, `i_comments` ) AGAINST( 'mountain' );
Empty set (0.05 sec)

mysql> SHOW CREATE TABLE `_images`;
CREATE TABLE `_images` (
  `i_id` int(11) NOT NULL auto_increment,
  `i_name` varchar(60) NOT NULL default '',
  `i_cat` varchar(15) NOT NULL default '',
  `i_location` varchar(30) NOT NULL default '',
  `i_kwords` text,
  `i_comments` text,
  `i_imagedate` varchar(18) NOT NULL default '||',
  `i_timestamp` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`i_id`),
  KEY `i_kwords_2` (`i_kwords`(1),`i_comments`(1)),
  FULLTEXT KEY `i_location` (`i_location`),
  FULLTEXT KEY `i_comments` (`i_comments`),
  FULLTEXT KEY `i_kwords` (`i_kwords`),
  FULLTEXT KEY `foo` (`i_kwords`,`i_comments`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql>
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Thanks feyd, works now as you have show, I never noticed the type as INDEX.

Thanks to Pimptastic also.
Post Reply