Is naming an index required?

Question

I am a little bit confused about naming an index if it's required or not. In stackoverflow and here, I saw adding an index like this: (through coding)

    ADD INDEX index_name (column_name, ...)
    //whether a per-column or multiple-column index

But, when I tried to add an index using phpMyadmin interface, I got this:

As you can see there is no name while adding the index here, so my problem here is naming an index required? If so, does the image above gives a default name for an index if nothing is given by an administrator? And what it's default name?

Answer 1

Using the ALTER TABLE .. ADD INDEX there is no requirement to add a name.

If you look at the MySQL documentation for ALTER TABLE you can see that the name parameter is optional (between square brackets), just like the type and options:

| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...

When you add an index without specifying a name MySQL will generate a name based on the name of the first field in the index. If multiple indexes start with the same field the index name will be suffixed by a sequential number.

For example running this code:

CREATE TABLE `index_demo` (
  `IdField` int(11) DEFAULT NULL,
  `DateField` date DEFAULT NULL,
  `GroupField` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `index_demo` ADD INDEX (`IdField`, `DateField`);

ALTER TABLE `index_demo` ADD INDEX (`IdField`, `GroupField`);

Will result in a table with 2 indexes, IdField and IdField_2 as evidenced by the resulting create table statements:

CREATE TABLE `index_demo` (
  `IdField` int(11) DEFAULT NULL,
  `DateField` date DEFAULT NULL,
  `GroupField` char(1) DEFAULT NULL,
  KEY `IdField` (`IdField`,`DateField`),
  KEY `IdField_2` (`IdField`,`GroupField`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please note, the name parameter is only optional when using the ALTER TABLE syntax and not the CREATE INDEX syntax as again documented in the documentation (notice the lack of square brackets)

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

I would however consider it a good practice to still name your indexes instead of relying on the default naming scheme.