MySQL FullText index find and create

Find all Full Text Indexes and build create index statements.


This is useful for when we create a new innodb database, 
convert some of the tables to MyISAM 
and setup full text indexes on a new replica.

Make it MyISAM!
SELECT 
CONCAT('ALTER TABLE `',TABLE_SCHEMA,'.',TABLE_NAME,'` ENGINE=MyISAM')
FROM 
information_schema.statistics 
WHERE index_type LIKE 'FULLTEXT%'
GROUP BY TABLE_SCHEMA, TABLE_NAME
create index statements.
SELECT 
CONCAT('ALTER TABLE `',TABLE_SCHEMA,'.',TABLE_NAME,'` ADD FULLTEXT `',INDEX_NAME,'` (`',GROUP_CONCAT(COLUMN_NAME,'`,`'),'`)')
FROM 
information_schema.statistics 
WHERE index_type LIKE 'FULLTEXT%'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME

Popular Posts