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.
CREATE INDEX idx_orders_customer ON orders (customer_id); CREATE UNIQUE INDEX idx_customers_email ON customers (email);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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
UNIQUEconstraints 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 onemail; 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
EXPLAINwhether the planner uses the index; the planner decides, not you. - Cost: more disk space and slower writes. Remove unused indexes with
DROP INDEX.
Related
DROP INDEX,EXPLAIN,CREATE TABLE,PRIMARY KEY,UNIQUE- Lessons: Indexes, Advanced indexing, Query plans