KEY and INDEX are synonyms in MySQL.
They mean the same thing. In the database you would use indexes to improve data recovery speed. Home
An index is typically created on columns used in JOIN, WHERE e ORDER BY clauses.
Important:
You can only have one primary key per table, but several unique constraints.
There is a very important difference between a Unique Index (MySQL respond to a "unique constraint") and a primary key in MySQL.
Take a look at this:
Create a table t with a indice unique in columns a,b (The combination of columns a, b should uniquely identify any tuple in the table, right?)
CREATE TABLE t (
a int,
b int,
c int,
UNIQUE KEY a (a,b)
);
Now let's enter data:
mysql> insert into t (a,b,c)values(1,2,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (c)values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (a,c)values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t (b,c)values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t (b,c)values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t (a,b,c)values(1,2,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'a'
mysql> select * from t;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 2 | 3 |
| NULL | NULL | 1 |
| 1 | NULL | 1 |
| NULL | 1 | 1 |
| NULL | 1 | 1 |
+------+------+------+
5 rows in set (0.00 sec)
mysql>
A unique index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This restriction does not apply to NULL values, except for the BDB store engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL .
Unique Unique Unique Constraints :
With
MyISAM as the engine, there should not be a performance difference between Unique vs Unique Constraints .
MyISAM does not treat them differently.
If you were using the
InnoDB engine, however, there would be a difference, because
InnoDB stores the data in primary key order.
