How to copy indexes from one identical table to another existing identical table

Question

I have 250 or so tables (mysql) that all share the exact same structure, except for indexes. One of the tables has all the correct, current and up-to-date indexes, and I want to propagate that index structure to the other 249 tables.

Everything I've found searching is about copying a table with its content and index structure, which I don't want to do (the data in each table is different, only the structure is identical), or creating a NEW table with the same structure, which I also don't want to do.

I'm hoping there's a way to use ALTER TABLE to tell mysql to mimic the table structure of the correct table and apply that to the other table, optimize the table, and then run these commands through a php script to apply to all 249 tables, but I can't figure out if there's an ALTER TABLE command that will do this.

Any tips?

Answer 1

On the "good" table run SHOW CREATE TABLE [table_name]. Copy the index lines and use those in a script to run the equivalent ALTER TABLE CREATE INDEX for every other table.

If you want the whole thing completely automated you'd have to parse the index definitions from a SHOW INDEXES or SHOW CREATE TABLE call. But I'm assuming you just need to run this once since it's such an odd situation.