FULL INDEX not working, and this odd Error message

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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

FULL INDEX not working, and this odd Error message

Post by simonmlewis »

We are trying to use FULL INDEX on a web site, but it's not working.
When I try to set full index on a field via mysql code, I get this message:

[text]#1214 - The used table type doesn't support FULLTEXT indexes[/text]
What does that mean. Have I set the table up wrong??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: FULL INDEX not working, and this odd Error message

Post by Celauran »

What table type are you using? What version of MySQL? What column type are you trying to set the index on?
Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.
https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: FULL INDEX not working, and this odd Error message

Post by simonmlewis »

How do I find out if the table is MyISAM? I've no idea. Never had this sort of issue before.

The field is VARCHAR.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: FULL INDEX not working, and this odd Error message

Post by Celauran »

Code: Select all

SHOW TABLE STATUS WHERE Name = 'table_name_here';
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: FULL INDEX not working, and this odd Error message

Post by simonmlewis »

And what am I looking for in the result, as I don't see anything about MyISAM?

EDIT:
Got it. It's InnoDB.

Is this easy to change?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: FULL INDEX not working, and this odd Error message

Post by simonmlewis »

Cracked it. Found the fix. And it works. Cheers.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply