Difference between CREATE INDEX and ALTER TABLE ADD INDEX in MySQL

MySQL

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

CREATE INDEX is essentially mapped to an ALTER TABLE statement to create indexes.

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:

SQL
-- 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:

SQL
-- 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:

SQL
-- 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);
The act of creating an index itself doesn’t necessarily lock the entire table for the entire duration of the index creation. Instead, it might impose locks on the rows being indexed to ensure data consistency.

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

See also  How to See Running Queries in MySQL - SHOW PROCESSLIST

See more:

Leave a Reply

Your email address will not be published. Required fields are marked *