CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?

Question

Just came across a weird issue, whereby depending on how I create an index, an index name is required.

http://dev.mysql.com/doc/refman/5.5/en/create-index.html

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

CREATE INDEX `random_name` ON `my_table` (`my_column`); # Requires an index name

ALTER TABLE `my_table` ADD INDEX (`my_column`); # Does not require an index name

It seems to me that the CREATE INDEX call, should not make index name required. I'm wondering if this is a MySQLism, or a SQL standard?

Answer 1

I don't think that the SQL standard defines how to create indexes at all.

A quote from this Wikipedia page:

Standardization

There is no standard about creating indexes because the ISO SQL Standard does not cover physical aspects. Indexes are one of the physical parts of database conception among others like storage (tablespace or filegroups). RDBMS vendors all give a CREATE INDEX syntax with some specific options which depends on functionalities they provide to customers.

The Postgres manual seems to support this here:

There are no provisions for indexes in the SQL standard.

More evidence under this related question on SO.

Answer 2

If you don't provide a name for the index, MySQL will automatically name the index for you.

ALTER TABLE my_table ADD INDEX (col1);
ALTER TABLE my_table ADD INDEX (col1, col2);
ALTER TABLE my_table ADD INDEX (col2);

SHOW INDEX FROM my_table;

The ALTER TABLE ADD INDEX statements above will produce the following indexes:

• col1
• col1_2
• col2

If you create an index with only one column, it might not be necessary to provide a name for the index (since the name of the index will be the same as the column name). But if you create an index with multiple columns, it might be better to provide a name for the index so it will be easier to identify the index.

It is good practice to provide a name for the index (and prefix it with idx or something to identify it as an index):

ALTER TABLE my_table ADD INDEX idx_my_column (my_column);

-- or
CREATE INDEX idx_my_column ON my_table (my_column);