1

I did SHOW INDEX on a 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:

Given this information, how do I structure the ALTER statement to add an Index to the table?

Mr_Thomas
  • 857
  • 3
  • 19
  • 39
  • Which column(s) do you want to add an index on? – user3783243 Apr 22 '22 at 14:53
  • In this example, the `ID` column, but there are several others. I was just trying to figure out what information from this list is necessary for the `ALTER` statement. – Mr_Thomas Apr 22 '22 at 14:54
  • In that case `id` already is indexed. https://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql – user3783243 Apr 22 '22 at 15:21

2 Answers2

1

The SHOW INDEX doesn't have 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 would need to fill in the schema and table name where I left placeholders ?, ?.

This is just to get you started. I know it doesn't account for a few options including prefix indexes, expression indexes, or comments. I'll leave that as an exercise for the reader.

Also it would generate a separate alter table statement for each index. If you want to do one alter table to add all indexes, use a subquery to generate the column list for each index, and then group_concat() to combine them in the outer query.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Doesn't seem to like the `WHERE ` clause: where `(table_schema, table_name) = (id, logfile)` – Mr_Thomas Apr 22 '22 at 15:40
  • Those are supposed to be quoted strings that name the schema and table name for which you want the indexes. You seem to have them as unquoted which makes them identifiers, but there are no such columns in the information_schema.statistics table. – Bill Karwin Apr 22 '22 at 15:58
  • I put single quotes around those strings and the statement DID process (no errors), but all I see is a column titled "_ddl" with nothing underneath. – Mr_Thomas Apr 22 '22 at 16:07
  • Is your schema name "id"? – Bill Karwin Apr 22 '22 at 16:13
  • No, that's the column I want to have an Index on. Where do I find the Table Schema? – Mr_Thomas Apr 22 '22 at 17:13
  • In the MySQL client you choose the schema (or database, these are synonyms in MySQL) when you use `USE ;` You can subsequently query the current schema: `SELECT SCHEMA();` or `SELECT DATABASE();` – Bill Karwin Apr 22 '22 at 17:32
0

I have expanded on Bill's good answer above. Output options are 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 = '<add your table schema here>' 
group by table_schema, table_name, index_name, non_unique
order by table_schema, table_name, non_unique asc
David Fear
  • 11
  • 2