SQL CREATE INDEX

As mentioned in the Index overview page, a table index helps SQL statements run faster. The syntax for creating an index is:

CREATE INDEX "index_name" ON "table_name" (column_name);

Note that an index can only cover one table. We cannot build an index that covers multiple tables.

Let's assume that we have the following table,

Table Customer

and we want to create an index on the column Last_Name, we would type in,

CREATE INDEX IDX_CUSTOMER_LAST_NAME
ON Customer (Last_Name);

If we want to create an index on both City and Country, we would type in,

CREATE INDEX IDX_CUSTOMER_LOCATION
ON Customer (City, Country);

A simple index is an index on a single column, while a composite index is an index on two or more columns. In the examples above, IDX_CUSTOMER_LAST_NAME is a simple index because there is only one column, while IDX_CUSTOMER_LOCATION is a composite index because there are two columns.

There is no strict rule on how to name an index. The generally accepted method is to place a prefix, such as "IDX_", before an index name to avoid confusion with other database objects. It is also a good idea to provide information on which table and column(s) the index is used on.

Please note that the exact syntax for CREATE INDEX may be different for different databases. You should consult with your database reference manual for the precise syntax.