CREATE INDEX

Creates an index.

CREATE INDEX index_name ON table_name (column_name);

Creates a unique index that disallows duplicate values.

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Creates a composite index covering multiple columns.

CREATE INDEX index_name ON table_name (column1, column2);

Drops an index (MySQL).

DROP INDEX index_name ON table_name;

Drops an index (PostgreSQL).

DROP INDEX index_name;

The following members table is used in the examples below.

Creates an index on the email column, which is frequently searched.

sample_create_index.sql

CREATE INDEX idx_members_email ON members (email);

Query OK, 0 rows affected

Creates a unique index to prevent duplicate email values.

sample_create_index.sql

CREATE UNIQUE INDEX idx_members_email_unique ON members (email);

Query OK, 0 rows affected

Creates a composite index on name and email.

sample_create_index.sql

CREATE INDEX idx_members_name_email ON members (name, email);

Query OK, 0 rows affected

Lists all indexes on the members table (MySQL).

sample_create_index.sql

SHOW INDEX FROM members;

+---------+------------+--------------------------+--------------+-------------+
| Table   | Non_unique | Key_name                 | Seq_in_index | Column_name |
+---------+------------+--------------------------+--------------+-------------+
| members |          0 | PRIMARY                  |            1 | member_id   |
| members |          0 | idx_members_email_unique |            1 | email       |
| members |          1 | idx_members_email        |            1 | email       |
| members |          1 | idx_members_name_email   |            1 | name        |
| members |          1 | idx_members_name_email   |            2 | email       |
+---------+------------+--------------------------+--------------+-------------+
5 rows in set

Drops an index that is no longer needed (MySQL).

sample_create_index.sql

DROP INDEX idx_members_email ON members;

Query OK, 0 rows affected

CREATE INDEX and CREATE UNIQUE INDEX work the same way across MySQL, PostgreSQL, and SQLite.

CREATE INDEX idx_members_email ON members (email);
CREATE UNIQUE INDEX idx_members_email_unique ON members (email);

The DROP INDEX syntax differs between databases. MySQL requires ON table_name, while PostgreSQL and SQLite only need the index name.

-- MySQL (ON table_name is required).
DROP INDEX idx_members_email ON members;

-- PostgreSQL, SQLite (table name is not needed).
DROP INDEX idx_members_email;

The command to list indexes also varies by database.

-- MySQL.
SHOW INDEX FROM members;

-- PostgreSQL.
SELECT * FROM pg_indexes WHERE tablename = 'members';

-- SQLite.
PRAGMA index_list('members');

Indexes are most effective on columns used in WHERE clauses, JOINs, and ORDER BY. However, adding too many indexes slows down INSERT, UPDATE, and DELETE operations, because each data change requires the indexes to be updated as well. Apply indexes only to the columns that truly need them.

A primary key (PRIMARY KEY) automatically gets an index. Adding an index to the referencing column of a foreign key (FOREIGN KEY) also improves JOIN performance.

To check whether a query is actually using an index, use EXPLAIN. For creating and modifying tables, see CREATE TABLE and ALTER TABLE.