CREATE INDEXIndexes & Search
Buy Me A Coffee

Create an index on one or more columns to speed up queries at the cost of write overhead.

Creates an index on one or more columns to speed up reads. Choose column order carefully for composite indexes. Consider index type (B-tree is default, GiST/GIN for full-text and JSON, hash for equality-only). Indexes slow down writes -- only create what you need.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all B-tree default. FULLTEXT and SPATIAL indexes also available. Prefix indexes required for TEXT/BLOB columns.
PostgreSQL ✓ Supported all Widest variety of index types: B-tree (default), Hash, GiST, GIN, BRIN, SP-GiST. Supports partial indexes (WHERE clause), expression indexes, and CREATE INDEX CONCURRENTLY (builds without locking the table).
SQL Server ✓ Supported all Clustered vs. non-clustered indexes. Filtered indexes (WHERE clause) since 2008. Columnstore indexes since 2012.
Oracle ✓ Supported all B-tree default. Function-based, bitmap, and domain indexes also supported.
SQLite ✓ Supported all B-tree only. Partial indexes (WHERE clause) supported since 3.8.9.

Details

PostgreSQL's CREATE INDEX CONCURRENTLY is uniquely useful in production — it builds the index without locking the table for writes. All engines support composite and unique indexes.

Standard Syntax

CREATE INDEX idx_name ON table_name (column1, column2);

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

B-tree default. FULLTEXT and SPATIAL indexes also available. Prefix indexes required for TEXT/BLOB columns.

CREATE INDEX idx ON tbl (col); CREATE UNIQUE INDEX idx ON tbl (col); CREATE FULLTEXT INDEX idx ON tbl (col);

PostgreSQL

Widest variety of index types: B-tree (default), Hash, GiST, GIN, BRIN, SP-GiST. Supports partial indexes (WHERE clause), expression indexes, and CREATE INDEX CONCURRENTLY (builds without locking the table).

CREATE INDEX idx ON tbl (col); CREATE INDEX idx ON tbl (col) WHERE active = true; -- partial CREATE INDEX idx ON tbl (LOWER(col)); -- expression CREATE INDEX CONCURRENTLY idx ON tbl (col); -- no lock

SQL Server

Clustered vs. non-clustered indexes. Filtered indexes (WHERE clause) since 2008. Columnstore indexes since 2012.

CREATE INDEX idx ON tbl (col); CREATE CLUSTERED INDEX idx ON tbl (col); CREATE INDEX idx ON tbl (col) WHERE active = 1; -- filtered

Oracle

B-tree default. Function-based, bitmap, and domain indexes also supported.

CREATE INDEX idx ON tbl (col); CREATE BITMAP INDEX idx ON tbl (col); CREATE INDEX idx ON tbl (UPPER(col)); -- function-based

SQLite

B-tree only. Partial indexes (WHERE clause) supported since 3.8.9.

CREATE INDEX idx ON tbl (col); CREATE INDEX idx ON tbl (col) WHERE active = 1;