SQL CREATE INDEX Keyword

The CREATE INDEX command is used to create indexes in SQL tables. Indexes significantly speed up data retrieval but don't change the data itself; they're like a table of contents for your data.

Creating an Index

Indexing a Single Column

This example shows how to create an index on a single column of a table. This index will make searching based on that column much faster.

Syntax


CREATE INDEX idx_lastname
ON Persons (LastName);

Output

An index named "idx_lastname" is created on the "LastName" column of the "Persons" table. Queries filtering by "LastName" will now be faster.

Indexing Multiple Columns

Indexes can span multiple columns. This creates a composite index, useful when your queries often filter by a combination of these columns.

Syntax


CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

Output

A composite index named "idx_pname" is created on the "LastName" and "FirstName" columns of the "Persons" table. Queries filtering by both columns will be significantly faster.

Important Notes on Indexes

The exact syntax for creating indexes might vary slightly depending on your specific database system (e.g., MySQL, PostgreSQL, SQL Server). Always check your database's documentation for the correct syntax.

Adding indexes increases the time it takes to update the table (inserts, updates, and deletes) because the index also needs to be updated. Therefore, it's best practice to only create indexes on columns frequently used in WHERE clauses of your queries.