Add new comment

On one of my Typo3 installations I found an unusually large InnoDB file (Typo3's indexed_search being the culprit).

dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html is a bit more complex. Instead of mysqldump (step 1 per documentation) I ran this SQL statement, following Nickolay Pelov's suggestion.

Here are the SQL statements I used to create the scripts to shrink this file:

use information_schema;

-- gather all innodb tables and make them myisam
select concat('alter table ', table_schema, '.', table_name, ' type=myisam;') from tables where engine='INNODB';

-- reset them to innodb
select concat('alter table ', table_schema, '.', table_name, ' type=innodb;') from tables where engine='INNODB';

Note that this fails if you have foreign keys on your InnoDB tables because MyISAM doesn't support them.