CODEWITHSUNDEEP
mysqlsql
hamburger
hamburger

Reputation: 1425

What does the CREATE TABLE KEY-Nameing in MySQL?

I found the following create statement and have two questions.

CREATE TABLE signups (
  signup_id bigint(20) NOT NULL auto_increment,
  domain varchar(200) NOT NULL default '',
  path varchar(100) NOT NULL default '',
  activation_key varchar(50) NOT NULL default '',
  PRIMARY KEY  (signup_id),
  KEY activation_key (activation_key),
  KEY domain_path (domain(140),path(51))
) $charset_collate;"

Why do I have to name the KEY activation_key? Does it make any sense and do I have access to the name in further statements? Or is it pure syntax?

And what does it mean if I name it like:

KEY domain_path (domain(140),path(51))

I understand that I have a double key here but what is the 140 and 51 doing here?

Upvotes: 1

Views: 49

Answers (2)

O. Jones
O. Jones

Reputation: 108641

Why do I have to name the KEY activation_key?

You don't. You can name it anything you want.

Do I have access to the name in further statements?

It may turn up in the output of EXPLAIN statements. You can use it later on in DROP INDEX statements. You could, if you loved trouble, use it in USE INDEX() pragmas in queries.

And what does it mean if I name it like: KEY domain_path (domain(140),path(51))

what is the 140 and 51 doing here?

Those numbers mean your index is a prefix index. It only covers that number of characters at the beginning of each column.

Upvotes: 1

Mjh
Mjh

Reputation: 2945

Every index has a name. It's used by MySQL for certain commands so the index can be referenced, such as when you're altering table and dropping a key - you have to specify which key to drop, and that's done by providing its name.

Always use meaningful names. We often skip key names so MySQL will give them names by default, those consist of columns covered by the index.

The KEY domain_path (domain(140),path(51)) means "take 140 chars at most from domain column, then take at most 51 characters from path column and add it to index structure".

You can read more about this syntax in the manual, it's mentioned at the very first example.

Upvotes: 3

Related Questions