How do you create an index in SQL?

SQLJunior~1 min read

To create an index in SQL, use the CREATE INDEX command. It specifies the index name, the table, and the columns it's built on.

sql

CREATE INDEX index_name
ON table_name (column);

sql

CREATE INDEX idx_users_email
ON users (email);

Creates an index named idx_users_email on the email column of the users table.

You can index several columns at once:

sql

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

This is a composite index.

If you need uniqueness (to prevent duplicates):

sql

CREATE UNIQUE INDEX idx_users_username
ON users (username);

Indexes speed up SELECT, but slow down INSERT, UPDATE, DELETE a bit.
The index name (idx_...) is arbitrary, but it's conventional to include the table and column names.

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.