5

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?

elixenide
  • 44,308
  • 16
  • 74
  • 100
developer098
  • 773
  • 2
  • 10
  • 18

2 Answers2

9

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.

Community
  • 1
  • 1
elixenide
  • 44,308
  • 16
  • 74
  • 100
  • ... So is index() then a non-clustered index? And is there a way to create a clustered index besides the primary key? – developer098 Dec 22 '16 at 19:23
  • 1
    @programmer321 Correct, simply using `INDEX()` gets you a secondary (i.e., non-clustered) index. By definition, only the primary key can be a clustered index, so, no, there's no way to create a non-primary-key clustered index. – elixenide Dec 22 '16 at 19:25
  • I upvoted, but with the minor quibble that this isn't "by definition" — it's just the only option given the way InnoDB is currently implemented. Other RDBMS products do support a clustered index that isn't the primary key. There are even some database products that support *multiple* clustered indexes. – Bill Karwin Dec 22 '16 at 19:34
  • To add, likewise in `SQL Server` you don't have option here to create a clustered index. – Rahul Dec 22 '16 at 19:34
  • 1
    @BillKarwin Well, to be clear, I meant "by definition" in MySQL, as in, it's literally in the definition in the MySQL docs. I wasn't trying to address how other RDBMSes do things, which are sometimes very, very different. – elixenide Dec 22 '16 at 19:36
  • 1
    Yep! Indexes in general are all vendor-specific extensions to SQL. The SQL specification does not mention indexes anywhere! – Bill Karwin Dec 22 '16 at 19:38
1

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.

Rahul
  • 76,197
  • 13
  • 71
  • 125