Page 1 of 1
Can't find FULLTEXT index matching the column list
Posted: Thu Mar 30, 2006 8:28 am
by mikebr
Pimptastic | Please use 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
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Posted: Thu Mar 30, 2006 8:32 am
by JayBird
Is "i_comments" definately set as a FULLTEXT index on your server database?
Posted: Thu Mar 30, 2006 8:42 am
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
Posted: Thu Mar 30, 2006 10:04 am
by feyd
the indexes are separate from each other, you'll need to make separate match() statements for each, I believe.
Posted: Thu Mar 30, 2006 10:57 am
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?
Posted: Thu Mar 30, 2006 11:02 am
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
Posted: Thu Mar 30, 2006 11:20 am
by feyd
post the create table of this and the query or queries you're trying to use now.
Posted: Thu Mar 30, 2006 11:45 am
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' )
Posted: Thu Mar 30, 2006 12:07 pm
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>
Posted: Thu Mar 30, 2006 12:36 pm
by mikebr
Thanks feyd, works now as you have show, I never noticed the type as INDEX.
Thanks to Pimptastic also.