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 NameData Type
First_Namechar(50)
Last_Namechar(50)
Addresschar(50)
Citychar(50)
Countrychar(25)
Birth_Datedatetime

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




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