Walkthough on what is MUL, PRI and UNI in MySQL?
From the documentation MySQL 5.7:
- If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY.
- If Key is UNI, the column is the first column of a UNIQUE index. (A UNIQUE index permits multiple NULL values, but you can tell whether the column permits NULL by checking the Null field.)
- If Key is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
Live Examples
Control group, this example has neither PRI, MUL, nor UNI:
mysql> create table penguins (foo INT);
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with one column and an index on the one column has a MUL:
mysql> create table penguins (foo INT, index(foo));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with a column that is a primary key has PRI
mysql> create table penguins (foo INT primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with a column that is a unique key has UNI:
mysql> create table penguins (foo INT unique);
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
A table with an index covering foo and bar has MUL only on foo:
mysql> create table penguins (foo INT, bar INT, index(foo, bar));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
| bar | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
A table with two separate indexes on two columns has MUL for each one
mysql> create table penguins (foo INT, bar int, index(foo), index(bar));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
| bar | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
A table with an Index spanning three columns has MUL on the first:
mysql> create table penguins (foo INT,
bar INT,
baz INT,
INDEX name (foo, bar, baz));
Query OK, 0 rows affected (0.01 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| foo | int(11) | YES | MUL | NULL | |
| bar | int(11) | YES | | NULL | |
| baz | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
A table with a foreign key that references another table's primary key is MUL
mysql> create table penguins(id int primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> create table skipper(id int, foreign key(id) references penguins(id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc skipper;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> desc penguins;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
Stick that in your neocortex and set the dial to "frappe".
