SQL Create Index

In SQL, an index is a database object that can be created on one or more columns of a table. An index is used to speed up queries by allowing the database to quickly locate rows based on the values in the indexed columns.

Here is an example of how to create an index on a table:

CREATE INDEX idx_last_name
ON employees (last_name);

In this example, we are creating an index called idx_last_name on the last_name column of the employees table. This index will speed up queries that involve searching or sorting by last name.

You can also create an index on multiple columns by specifying them in the CREATE INDEX statement:

CREATE INDEX idx_first_last_name
ON employees (first_name, last_name);

In this example, we are creating an index called idx_first_last_name on both the first_name and last_name columns of the employees table. This index will speed up queries that involve searching or sorting by both first name and last name.

Indexes can be created on either single or multiple columns, and they can be unique or non-unique. A unique index ensures that the indexed columns contain only unique values, while a non-unique index allows duplicate values.

You can remove an index from a table using the DROP INDEX command:

DROP INDEX idx_last_name
ON employees;

In this example, we are removing the idx_last_name index from the employees table.

By using indexes, you can improve the performance of queries on large tables by reducing the number of rows that need to be examined. However, creating too many indexes or using them improperly can also have a negative impact on performance. Therefore, it's important to create indexes strategically and to monitor their usage over time.