mattgadient.com

How to remove a fulltext index in MySQL – switching from MyISAM to InnoDB

So, I decided to uninstall YARPP (the “yet another related posts plugin” for WordPress), and convert my wp_posts table from MyISAM back into InnoDB.

Of course, when I went to do the MyISAM -> InnoDB conversion, I got an error from myPhpAdmin:

The used table type doesn’t support FULLTEXT indexes

Not unexpected… versions of MySQL < 5.6.4 don’t support FULLTEXT in InnoDB, so I knew I had to drop the FULLTEXT indexes but didn’t quite know how. And it took a while to find the solution. But here it is:

 

1) Run the following query to get a list of your tables:

 SHOW CREATE TABLE wp_posts

 

(if you didn’t use the default wordpress table prefix or aren’t doing this for WordPress, replace wp_posts with… whatever table you’re having a problem with)

 

2) It will show you a list of the tables. You’re looking for something like this:

 

finding-fulltext

You can see the FULLTEXT part – the names of the index keys are yarpp_title and yarpp_content in this case.

 

3) Now to drop these fulltext keys, the following queries:

ALTER TABLE wp_posts DROP INDEX yarpp_title;
ALTER TABLE wp_posts DROP INDEX yarpp_content;

I did them one at a time. Obviously, if you’re not in 100%-the-same-boat-as-me, replace wp_posts¬†with your table name, and yarpp_title / yarpp_content with whatever showed up in your results.

 

4) Exciting!

Assuming you got all the FULLTEXT keys taken care of, the following should work to convert the table to InnoDB now:

ALTER TABLE wp_posts ENGINE=InnoDB;

 

(assuming once again, it’s the wp_posts table you’re converting – if not, use the name of the table you’re converting)

…and, done!