What\'s the Difference Between `INDEX` and `KEY` in MySQL?

The difference between INDEX and KEY in MySQL

In MySQL, INDEX and KEY are synonyms used to improve query performance, they have no essence the difference. However, for ISO SQL compatibility reasons, using INDEX is preferred.

The description of CREATE TABLE in the MySQL manual states: "KEY is usually a synonym for INDEX. When specified in the column definition, the primary key attribute PRIMARY KEY can also be specified as KEY. This is for compatibility with other database systems. Implemented. ”

For example, the following three CREATE TABLE statements are equivalent and produce the same table object in the database:

CREATE TABLE orders1 (
    order_id int PRIMARY KEY
);

CREATE TABLE orders2 (
    order_id int KEY
);

CREATE TABLE orders3 (
    order_id int NOT NULL,

    PRIMARY KEY ( order_id )
);

On the other hand, the following two statements are equivalent but different from the above three statements:

CREATE TABLE orders4 (
    order_id int NOT NULL,

    KEY ( order_id )
);

CREATE TABLE orders5 (
    order_id int NOT NULL,

    INDEX ( order_id )
);

This is because in these statements, KEY and INDEX are synonyms of INDEX, not PRIMARY KEY. Therefore, the KEY ( order_id ) and INDEX ( order_id ) members do not define a primary key, they only define a generic index object, which is completely different from KEY (because it does not uniquely identify a row).

You can verify this by running the SHOW CREATE TABLE orders1...5 command:

CREATE TABLE orders1 (
    order_id int NOT NULL,
    PRIMARY KEY ( order_id )
)


CREATE TABLE orders2 (
    order_id int NOT NULL,
    PRIMARY KEY ( order_id )
)


CREATE TABLE orders3 (
    order_id int NOT NULL,
    PRIMARY KEY ( order_id )
)


CREATE TABLE orders4 (
    order_id int NOT NULL,
    KEY ( order_id )
)


CREATE TABLE orders5 (
    order_id int NOT NULL,
    KEY ( order_id )
)

The above is the detailed content of What\'s the Difference Between `INDEX` and `KEY` in MySQL?. For more information, please follow other related articles on the PHP Chinese website!