INDEX vs. KEY in MySQL: What\'s the Difference and Which Should I Use?

Using INDEX vs KEY in MySQL

Introduction

MySQL provides two syntaxes for creating indexes on tables: INDEX and KEY. While both achieve the same functionality, there are subtle differences between them.

Synonyms vs. Extensions

The primary difference between INDEX and KEY lies in their nature. INDEX is considered the ISO SQL-compliant term, while KEY is a MySQL-specific extension. This means that using INDEX ensures portability across different database systems, while KEY is limited to MySQL.

Usage

However, in practice, the usage of INDEX and KEY is interchangeable. They both create indexes on columns to improve query performance by providing fast lookups. The syntax for their usage is identical, as seen in the following example:

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),
    KEY parent (parent_id)  -- or INDEX parent (parent_id)
)

Primary Key Consideration

While INDEX and KEY can be used interchangeably for creating indexes, there is an exception when dealing with primary keys. The PRIMARY KEY keyword can be abbreviated as KEY in column definitions for compatibility with other database systems. For example:

CREATE TABLE orders (
    order_id int PRIMARY KEY,
    order_date timestamp
);

In this case, KEY is a synonym for PRIMARY KEY and creates a unique index on the order_id column, which uniquely identifies each row in the table.

Conclusion

In summary, while both INDEX and KEY serve the same purpose, INDEX is preferred due to its ISO SQL compliance. However, in most practical scenarios, the choice between them is a matter of personal preference or adherence to specific standards. Understanding the difference between their usage and the context in which they are used will help you effectively optimize database performance.

The above is the detailed content of INDEX vs. KEY in MySQL: What's the Difference and Which Should I Use?. For more information, please follow other related articles on the PHP Chinese website!