How to convert SHOW INDEX to ALTER TABLE to add index in MySQL

Question

I executed SHOW INDEX on the table and this is the output I got:

Table: logfile
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 759103
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

Based on this information, how do I construct an ALTER statement to add an index to the table?

Answer 1

I've expanded on Bill's good answer above. Output options have been expanded to include ADD PRIMARY KEY, ADD UNIQUE INDEX, or ADD INDEX

select concat('ALTER TABLE ', table_schema, '.', table_name, ' ADD ',
  if(index_name = 'PRIMARY', 'PRIMARY KEY ', if(non_unique, 'INDEX ', 'UNIQUE INDEX ')),
  if (index_name = 'PRIMARY','', index_name), ' (', group_concat('', column_name, '' order by seq_in_index), ');')
  as 'alter table statement'
from information_schema.statistics
where table_schema = ''
group by table_schema, table_name, index_name, non_unique
order by table_schema, table_name, non_unique asc

Answer 2

SHOW INDEX Not enough information. You can try this:

select concat('ALTER TABLE `', table_schema, ``.`', table_name, '' ADD ',
  if(non_unique, '', 'UNIQUE '), 'INDEX `', index_name, '' (',
  group_concat('`', column_name, '`' order by seq_in_index), ');') as _ddl
from information_schema.statistics
where (table_schema, table_name) = (?, ?)
group by table_schema, table_name, index_name, non_unique;

You need to fill in the schema and table names I left placeholders ?, ?

This is just to get you started. I know it doesn't consider some options including prefix indexing, expression indexing, or annotations. I'll leave it as an exercise to the reader.

It also generates a separate alter table statement for each index. If you want to make an alter table that adds all indexes, use a subquery to generate the column list for each index, and then group_concat() combine them in the outer query.