mysqlindexingkey

What's the difference between using INDEX vs KEY in MySQL?


I know how to use INDEX as in the following code. And I know how to use foreign key and primary key.

CREATE TABLE tasks ( 

    task_id        int unsigned NOT NULL AUTO_INCREMENT, 
    parent_id      int unsigned NOT NULL DEFAULT 0, 
    task           varchar(100) NOT NULL, 
    date_added     timestamp    NOT NULL, 
    date_completed timestamp        NULL, 

    PRIMARY KEY ( task_id ), 
    INDEX parent ( parent_id )
)

However I found a code using KEY instead of INDEX as following.

CREATE TABLE orders (
    
    order_id        int unsigned NOT NULL AUTO_INCREMENT,
    -- etc 

    KEY order_date ( order_date )
)

I could not find any explanation on the official MySQL page. Could anyone tell me what is the differences between KEY and INDEX?

The only difference I see is that when I use KEY ..., I need to repeat the word, e.g. KEY order_date ( order_date ).


Solution

  • There's no difference. They are synonyms, though INDEX should be preferred (as INDEX is ISO SQL compliant, while KEY is a MySQL-specific, non-portable, extension).

    From the CREATE TABLE manual entry:

    KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.


    By "The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition.", it means that these three CREATE TABLE statements below are equivalent and generate identical TABLE objects 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 )
    );
    

    ...while these 2 statements below (for orders4, orders5) are equivalent with each other, but not with the 3 statements above, as here KEY and INDEX are synonyms for INDEX, not a PRIMARY KEY:

    CREATE TABLE orders4 (
        order_id int NOT NULL,
    
        KEY ( order_id )
    );
    
    CREATE TABLE orders5 (
        order_id int NOT NULL,
    
        INDEX ( order_id )
    );
    

    ...as the KEY ( order_id ) and INDEX ( order_id ) members do not define a PRIMARY KEY, they only define a generic INDEX object, which is nothing like a KEY at all (as it does not uniquely identify a row).

    As can be seen by running SHOW CREATE TABLE orders1...5:

    Table SHOW CREATE TABLE...
    orders1 CREATE TABLE orders1 (
    order_id int NOT NULL,
    PRIMARY KEY ( order_id )
    )
    orders2 CREATE TABLE orders2 (
    order_id int NOT NULL,
    PRIMARY KEY ( order_id )
    )
    orders3 CREATE TABLE orders3 (
    order_id int NOT NULL,
    PRIMARY KEY ( order_id )
    )
    orders4 CREATE TABLE orders4 (
    order_id int NOT NULL,
    KEY ( order_id )
    )
    orders5 CREATE TABLE orders5 (
    order_id int NOT NULL,
    KEY ( order_id )
    )