AWSのDatabase Migration Serviceを利用してEC2からRDSにデータ移行したときに各テーブル内のインデックス情報が移行していなかったので、移行元のDBからCREATE INDEX構文を出力するSQLを作成しました。
テーブルに設定されているインデックスは通常
show index from "[table_name]";
といったクエリでテーブルごとに確認しますが、information_schemaからも取得できます。
select table_schema, table_name, index_name, column_name, seq_in_index from information_schema.statistics where table_schema = "[database_name]" and index_name != "PRIMARY" ;
上記のSQLをベースにCREATE INDEX構文のSQLを出力するのが以下です。
select CONCAT( "CREATE INDEX ", t1.index_name, " ON ", t1.table_name, "(", ( select GROUP_CONCAT(t2.column_name separator ', ') from information_schema.statistics as t2 where t2.index_name = t1.index_name and t2.table_name = t1.table_name order by t2.table_name, t2.index_name, t2.seq_in_index ), ");" ) as create_index_query from information_schema.statistics as t1 where table_schema = "[database_name]" and index_name != "PRIMARY" group by t1.table_name, t1.index_name ;
手ごろなデータベースがなかったのでwordpressのデータベースで実行しました。
最初マルチカラムインデックスのことを忘れていてカラムの並び順が保障されないようなSQLを作ってしまいましたが、上記のSQLはサブクエリ内のorder byで設定しているので私が知っている限りのインデックス構文には対応していると思います。移行するテーブルが1つ2つであれば手作業で作成してしまっても問題ないですが、大量にある場合はSQLで生成したほうが早いし間違いが起きにくいと思います。めったに使うことはないと思います。