Does INDEX() create a clustered or non-clustered index in MySQL?

Question

I am learning from a tutorial that uses INDEX() within a CREATE TABLE statement, but does not explain whether it is clustered or non-clustered. My question is: does INDEX() when used in a CREATE TABLE statement result in a clustered or non-clustered index?

For example:

CREATE TABLE test (a varchar(30), b varchar(30), index(a));

/* Is column A a clustered or non-clustered index? */

Also wondering how to do the opposite as well: if the example results in a non-clustered index, how do you write a clustered index, and vice versa?

Answer 1

TL;DR The primary key - and only the primary key - is a clustered index. If you don't explicitly define a primary key, the first suitable UNIQUE key is used. If you don't have either a primary key or a suitable UNIQUE key, MySQL generates a hidden clustered index. You cannot create a clustered index using INDEX().

As explained in the docs (emphasis added):

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.

...

• When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

• If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

...

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

See also the definition of clustered index in the glossary, which defines it as "The InnoDB term for a primary key index," along with some additional details.

So, to answer your question, there's no way to create a clustered index, other than to create a primary key or, on a table without a primary key, a suitable UNIQUE key (all key columns NOT NULL). INDEX() just creates a secondary (i.e., non-clustered) key, no matter what you do with it.

^{* Note: as pointed out in the comments, some other databases don't have clustered indexes, at all, and some allow more than one clustered index on a table. I'm only addressing MySQL in my answer.}

Answer 2

Is column A a clustered or non-clustered index?

It's a non-clustered index and only primary key field has clustered index. Remember, there can be only one clustered index in a table and thus it definitely can't create one.