SQL > ALTER TABLE > Add Index Syntax

Sometimes after a table has been created in a database, we find that it is advantageous to add an index to that table to speed up queries involving this table (to understand what an index is and how it can help speed up SQL queries, please see the INDEX section). To do this in SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD INDEX command to tell the RDBMS that we want to add an index.

Adding an index with ALTER TABLE ADD INDEX speeds up queries on frequently searched columns. Note that this syntax is supported in MySQL but not in Oracle or SQL Server, which use CREATE INDEX instead.

The syntax for adding an index to an existing table is, 

ALTER TABLE "table_name"
ADD INDEX "index_name" (column_name);

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(50) 
 City  char(50) 
 Country  char(25) 
 Birth_Date  datetime 

Assume we want to add an index on the "Country" column. To do this, we type in the following: 

ALTER TABLE Customer ADD INDEX IDX_COUNTRY (Country);

Please note that using ALTER TABLE to add an index is supported in MySQL but not in Oracle or SQL Server.

Frequently Asked Questions

Q: Does ALTER TABLE ADD INDEX work in Oracle and SQL Server?
A: No. ALTER TABLE ADD INDEX is a MySQL-specific syntax. In Oracle and SQL Server, you create indexes using the standalone CREATE INDEX statement.
Q: Why would I add an index to a table after it has been created?
A: As tables grow or query patterns change, you may find that certain columns are frequently used in WHERE clauses or JOIN conditions. Adding an index on those columns speeds up data retrieval significantly.
Q: Can I add an index on multiple columns?
A: Yes. A composite index covers multiple columns: ALTER TABLE Customer ADD INDEX IDX_NAME (Last_Name, First_Name);
Q: Does adding an index slow down INSERT and UPDATE operations?
A: Yes, slightly. Each index must be updated whenever data changes, so there is a write performance trade-off. Add indexes only on columns that are frequently queried.

Related Topics

Next: SQL DROP INDEX

This page was last updated on March 19, 2026.



Copyright © 2026   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact