Difference between CREATE INDEX and ALTER TABLE ADD INDEX in MySQL

In MySQL, there is no difference in the implementation on the server side between CREATE INDEX and ALTER TABLE ADD INDEX.

You can find more details in the official documentation.

The primary distinction lies in the syntax:

• With CREATE INDEX, you must specify a name for the index.
• With ALTER TABLE ADD INDEX, specifying a name is optional. If a name is not provided, the server generates a default name, using the name of the first column in the index with a number suffix if necessary.

However, there are some practical differences worth noting:

Batch Index Creation:

With CREATE INDEX, you can’t add two indexes in a single query. Each index requires a separate query. For example:

-- Create index on user_id
CREATE INDEX user_id_idx ON users (user_id);
-- Create index on status
CREATE INDEX status_idx ON users (status);

On the other hand, with ALTER TABLE ADD INDEX, you can create multiple indexes in a single query:

-- Create index on user_id and status
ALTER TABLE users
ADD INDEX user_id_idx (user_id),
ADD INDEX status_idx (status);

Efficiency:

Creating an index on a table can lock the table and take a significant amount of time, especially for large tables. To optimize this process, you can use the ALTER TABLE statement to create or drop multiple indexes in a single SQL query.

While every CREATE INDEX statement will lock the table, the ALTER TABLE statement will lock the table only once, potentially saving time for large-scale operations:

-- Using CREATE INDEX
CREATE INDEX user_id_idx ON users (user_id);
CREATE INDEX status_idx ON users (status);

-- Using ALTER TABLE
ALTER TABLE users
ADD INDEX user_id_idx (user_id),
ADD INDEX status_idx (status);

This distinction in syntax and functionality can be crucial depending on the specific requirements and scale of the database operations you are performing.

See more:

• How to check Max and Existing Connections in MySQL
• How to See Running Queries in MySQL – SHOW PROCESSLIST