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:

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.

Next: SQL DROP INDEX

This page was last updated on October 12, 2024.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.