29

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?

25

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.

  • 6
    MySQL is the only DBMS I know which allows adding an explicit index using ALTER TABLE (there are certain cases where implicit indexes will be created by various DBMS to support constraints, but that's not what I mean). – a_horse_with_no_name Jul 18 '13 at 21:25
  • @a_horse_with_no_name: Duly noted. I don't understand why MySQL forces us to pass an index_name, when clearly it can handle no name underneath. It's a PITA to be forced to do an alter simply to avoid naming indices. – Mike Purcell Jul 19 '13 at 6:31
  • I wonder if ALTER TABLE always creates a copy of the table and if CREATE INDEX avoids this behavior. – Horaciux Aug 14 '18 at 2:06
  • I'm pretty new to MySQL. In MS SQL Server, when you create a non clustered index (are this called secondary index in MySLQ?) the table isn't copied. Only modification on clustered index creates a restructured copy of the table. Is it possible in MySQL to create an index and without the cost of creating a copy of the table? Thanks – Horaciux Aug 14 '18 at 11:15
  • 1
    @Horaciux: Oh, I was thinking of PostgreSQL (missing the MySQL tag for the moment). MySQL has "COPY" and "INPLACE" operations for ALTER TABLE. You might start a new question to get the attention of MySQL experts here. – Erwin Brandstetter Aug 14 '18 at 14:29

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.