Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Data Definition

SQL CREATE INDEX statement

Reference for SQL CREATE INDEX: speed up lookups and sorts, unique, composite and partial indexes, checking use with EXPLAIN, and dialect differences.

CREATE INDEX builds a separate, sorted lookup structure on one or more columns of a table. The database can then find matching rows without reading the whole table, and can often return rows already in the order you asked for.

An index speeds up reads and slows down writes slightly, because every INSERT, UPDATE and DELETE must keep it current. Add indexes for columns that appear in selective WHERE conditions, join keys and ORDER BY clauses, not on everything.

SQL
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_customers_email ON customers (email);

Try it

SQL Create an index and see the query plan use it ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Unique, composite and partial indexes (fails on purpose at the end) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column_name [ASC | DESC] [, ...])
[WHERE condition];
Part Description
UNIQUE Rejects rows with duplicate key values. NULLs are treated as distinct in most databases (SQL Server allows only one).
index_name Name of the index; unique within the schema (SQLite, PostgreSQL) or per table (MySQL, SQL Server).
(column ...) One or more key columns. Order matters: an index on (a, b) helps filters on a, or a and b, but not on b alone.
ASC / DESC Sort direction of that column.
WHERE condition Partial index: only matching rows are indexed (SQLite, PostgreSQL, SQL Server filtered indexes). Not in MySQL or Oracle.

Dialect differences

Dialect Notes
SQLite B-tree only. Supports partial indexes and indexes on expressions.
PostgreSQL Also hash, GIN, GiST, BRIN. CREATE INDEX CONCURRENTLY avoids blocking writes. INCLUDE (cols) adds payload columns.
MySQL InnoDB B-tree, plus full-text and spatial indexes. No partial indexes; functional indexes since 8.0.13. INVISIBLE indexes available.
SQL Server Clustered and nonclustered indexes; filtered indexes use WHERE; INCLUDE for covering.
Oracle B-tree, bitmap and function-based indexes. No IF NOT EXISTS before 23ai.
SQL
-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status) INCLUDE (ordered_at);

-- SQL Server
CREATE NONCLUSTERED INDEX idx_orders_status ON orders (status) WHERE status = 'pending';

Usage notes

  • Primary keys and UNIQUE constraints already create an index; do not add a duplicate.
  • Foreign key columns are not indexed automatically in most databases, and joins and cascades on them benefit from an index.
  • Functions defeat plain indexes. WHERE LOWER(email) = 'x' cannot use an index on email; index the expression (SQLite, PostgreSQL) or store a normalized column.
  • Low-selectivity columns (a few distinct values, like a boolean) rarely benefit, unless used in a partial index.
  • Check with EXPLAIN whether the planner uses the index; the planner decides, not you.
  • Cost: more disk space and slower writes. Remove unused indexes with DROP INDEX.