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.
DROP INDEX
and for readingEXPLAIN
.