CREATE INDEX

Indexes & 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

Filter by Database
SQL CREATE INDEX Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all B-tree default. FULLTEXT and SPATIAL indexes also available. Prefix indexes required for TEXT/BLOB columns.
PostgreSQL Native 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 Native all Clustered vs. non-clustered indexes. Filtered indexes (WHERE clause) since 2008. Columnstore indexes since 2012.
Oracle Native all B-tree default. Function-based, bitmap, and domain indexes also supported.
SQLite Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;