SQL Add Index Syntax
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.
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:
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.