SQL: Indexes

This SQL tutorial explains how to create and drop indexes with syntax and examples.

What is an Index in SQL?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. Each index name must be unique in the database.

Create an Index

You can create an index in SQL using the CREATE INDEX statement.

Syntax

The syntax to create an index in SQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n);

UNIQUE

The UNIQUE modifier indicates that the combination of values in the indexed columns must be unique.

index_name

The name to assign to the index.

table_name

The name of the table in which to create the index.

column1, column2, ... column_n

The columns to use in the index.

Example

Let's look at an example of how to create an index in SQL.

For example:

CREATE INDEX websites_idx
  ON websites (site_name);

In this example, we've created an index on the websites table called websites_idx. It consists of only one field - the site_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX websites_idx
  ON websites (site_name, server);

This would create an index called websites_idx that is made up of two columns - site_name and server.

Unique Index

Similar to a primary key, a unique key allows you to choose one column or combination of columns that must be unique for each record. Although you can only have one primary key on a table, you can create as many unique indexes on a table as you need.

To create a unique index on a table, you need to specify the UNIQUE keyword in the CREATE INDEX statement.

For example:

CREATE UNIQUE INDEX websites_idx
  ON websites (site_name);

This example would create a unique index on the site_name field so that this field must always contains a unique value with no duplicates. This is a great way to enforce integrity within your database if you require unique values in columns that are not part of your primary key.

Drop an Index

You can drop an index in SQL using the DROP INDEX statement.

Syntax

The syntax to drop an index in SQL is:

For Oracle and PostgreSQL:

DROP INDEX index_name;

For MySQL and MariaDB:

DROP INDEX index_name
  ON table_name;

For SQL Server:

DROP INDEX table_name.index_name;

index_name

The name of the index to drop.

table_name

The name of the table that the index belongs to.

Example

Let's look at an example of how to drop an index called websites_idx from the websites table.

For Oracle:

DROP INDEX websites_idx;

Because each index name must be unique within the database, we do not have to specify the websites table in the DROP INDEX statement in Oracle.

For MySQL and MariaDB:

DROP INDEX websites_idx
  ON websites;

For SQL Server:

DROP INDEX websites.websites_idx;

As you can see, each database has unique differences in their syntax. Be sure you use the correct DROP INDEX command.