CODEWITHSUNDEEP
mysqlsql
Matt
Matt

Reputation: 2509

Trying to understand "Can't find FULLTEXT index matching the column list" error

As I'm trying to understand SQL by playing around with the code a bit, I have run into this situation. I run this code

SELECT * FROM jobs WHERE MATCH ( title, location, description ) AGAINST ( 'New York designer')

and I get the right row results. However, if I ask myself, "okay so what if I want just look at one column?" 

 SELECT * FROM  jobs WHERE  match(location) against ('designer')

I get the error "Can't find FULLTEXT index matching the column list". Why? I'm not sure what this error means. I just remove two words and commas.

I alter the jobs table to use the MyISAM engine. Does that mean it's searchable or "FULLTEXT"? Since InnoDB isn't searchable, is that correct to say?

But now, when isolated to searching to one column, it's a problem?

Let me know if this makes sense, I will re-edit.

Upvotes: 2

Views: 14183

Answers (2)

karthic
karthic

Reputation: 101

when everything was right and still got this error i found that the KEYS were disabled ..

make sure u have enabled the keys on that table

it didn't work for me when i had disabled the keys ...

i enabled the keys " ALTER TABLE table name ENABLE KEYS;

and it worked fine

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

FULLTEXT indexes are a feature of the MyISAM storage engine.

You can't match against the location column unless there is a FULLTEXT index on that column alone.

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

Upvotes: 7

Related Questions