MySql - Primary Key (clustered index or cluster index)

About

Logical Data Modeling - Primary Key (Id, Name) in MySql

Articles Related

Advice

From primary_key glossary - When choosing primary key values, consider:

• a synthetic key (using arbitrary values)
• rather than a natural key (ie relying on values derived from some other source)

Storage

For an InnoDB tables:

• InnoDB requires that every table has such an index (also called the clustered index or cluster index)
• the primary key determines the physical layout of rows in the data file. ie it organizes the table storage based on the column values of the primary key.

Example

CREATE TABLE phpgw_lang (
lang varchar(5) NOT NULL DEFAULT '',
app_name varchar(100) NOT NULL DEFAULT 'common',
message_id varchar(255) NOT NULL DEFAULT '',
content text,
PRIMARY KEY(lang,app_name(75),message_id(100))
);

where:

• the length in characters of the primary key is 180:
  • 5 from lang
  • 75 from app_name
  • 100 from message_id

Documentation / Reference

• https://dev.mysql.com/doc/refman/5.6/en/create-table.html#create-table-indexes-keys