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;

Syntax	Description
CREATE INDEX	Creates a standard index on a table. Multiple rows with the same value are allowed.
CREATE UNIQUE INDEX	Creates an index that does not allow duplicate values. Has the same effect as a UNIQUE constraint.
ON table_name (column_name)	Specifies the table and column to index.
Composite index	Combines multiple columns into a single index. Column order matters — the index is most effective when the leading column is used in the search.
DROP INDEX	Removes an index that is no longer needed. In MySQL, ON table_name is required.

-- Creates an index on the email column, which is frequently searched.
CREATE INDEX idx_members_email ON members (email);

-- Creates a unique index to prevent duplicate email values.
CREATE UNIQUE INDEX idx_members_email_unique ON members (email);

-- Creates a composite index on department and salary.
CREATE INDEX idx_employees_dept_salary ON employees (department, salary);

-- Lists all indexes on the members table (MySQL).
SHOW INDEX FROM members;

-- Drops the index that is no longer needed (MySQL).
DROP INDEX idx_members_email ON members;

-- Example output of SHOW INDEX FROM members; (partial)
-- +---------+------------+-------------------------+-------------+-------------+
-- | 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       |
-- +---------+------------+-------------------------+-------------+-------------+

CREATE INDEX and CREATE UNIQUE INDEX work the same way across MySQL, PostgreSQL, Oracle, SQL Server, 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, Oracle, and SQLite only need the index name.

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

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

SQL Server also does not require ON table_name, but uses a schema-qualified format.

-- SQL Server.
DROP INDEX idx_members_email ON members;

The command to list indexes also varies by database.

-- MySQL.
SHOW INDEX FROM members;

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

-- Oracle.
SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'MEMBERS';

-- SQL Server.
EXEC sp_helpindex '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.