3

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:

image

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?

1
  • 1
    The name (explicit or default) is needed only(?) for DROP INDEX and for reading EXPLAIN.
    – Rick James
    Jan 12 '17 at 0:52
3

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.

1
  • Yes, thanks, this is sure clarified my problems, ALTER TABLE = name parameter is optional and make a good practice to name the indexes. Jan 11 '17 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.