SQL CREATE INDEX

What does SQL CREATE INDEX do?

CREATE INDEX builds a secondary data structure on one or more table columns to accelerate data retrieval and, when UNIQUE, enforce value uniqueness.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CREATE INDEX: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, DB2, Snowflake (limited), Redshift, CockroachDB

SQL CREATE INDEX Full Explanation

CREATE INDEX generates an auxiliary object that stores sorted references to the underlying table rows. Query planners use this structure to locate matching rows faster than a full table scan, especially for predicates, joins, and orderings on the indexed columns. Indexes speed reads but add overhead to INSERT, UPDATE, DELETE, and VACUUM operations because the index must stay in sync with the base table. Most databases allow additional options such as UNIQUE (to disallow duplicate key values), IF NOT EXISTS (to skip creation if the index already exists), index method selection (BTREE, HASH, GIN, GiST, FULLTEXT, CLUSTERED, etc.), partial indexes via WHERE predicates, index expressions on computed values, and included (non-key) columns for covering indexes. Locking behavior varies: PostgreSQL blocks writes during creation unless CONCURRENTLY is used, MySQL builds the index while blocking writes for the affected table engine, SQLite locks the database for the operation. Always weigh the read performance gain against write slowdowns and extra disk usage.

SQL CREATE INDEX Syntax

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name [USING method]
  (column_or_expression1 [ASC|DESC] [NULLS FIRST|LAST],
   column_or_expression2 ...)
[INCLUDE (non_key_column [, ...])]
[WHERE partial_predicate];

SQL CREATE INDEX Parameters

index_name (identifier) - Name for the new index
table_name (identifier) - Target table or materialized view
method (enum) - Index type (BTREE, HASH, GIN, GiST, FULLTEXT, CLUSTERED, etc.)
column_or_expression (identifier or expression) - Key columns or expressions to index
ASC (DESC) - keyword|||Sort direction for each key column
NULLS FIRST (LAST) - keyword|||Placement of NULL values (PostgreSQL, Oracle)
INCLUDE (keyword list) - Non-key columns stored in the leaf pages to make a covering index (PostgreSQL, SQL Server)
WHERE partial_predicate (boolean expression) - Limits index entries to rows matching the predicate (partial index)
IF NOT EXISTS (clause) - Skip creation if the index already exists
UNIQUE (clause) - Enforce uniqueness across key columns

Example Queries Using SQL CREATE INDEX

-- Simple BTREE index
CREATE INDEX idx_users_email ON users(email);

-- Unique composite index
CREATE UNIQUE INDEX idx_orders_user_product
ON orders(user_id, product_id);

-- Partial index to speed queries on active rows
CREATE INDEX idx_sessions_active
ON sessions(last_seen)
WHERE is_active = TRUE;

-- Covering index with INCLUDE (PostgreSQL, SQL Server)
CREATE INDEX idx_pageviews_user_ts
ON pageviews(user_id, event_ts)
INCLUDE (device_type);

-- Concurrent creation to avoid write locks (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_logs_ts ON logs(event_ts DESC);

Expected Output Using SQL CREATE INDEX

Database engine adds the specified index object, returning a confirmation such as "CREATE INDEX"
Subsequent queries that filter, join, or sort on the indexed keys use the index to reduce I/O and CPU time

Use Cases with SQL CREATE INDEX

Speed up frequent WHERE, JOIN, and ORDER BY operations on large tables
Enforce business rules with UNIQUE indexes instead of separate constraints
Create covering indexes to satisfy queries without touching base rows
Isolate hot subsets of data with partial indexes for better cache usage
Support full-text search or geographic queries via specialized index methods

Common Mistakes with SQL CREATE INDEX

Creating indexes on small or rarely queried tables
Indexing columns with low selectivity (e.g., boolean flags)
Adding every column to a single wide index instead of targeted ones
Ignoring write-performance impact in high-ingest workloads
Forgetting column order matters for multi-column indexes
Assuming an index is used without checking the query plan
Failing to rebuild or drop unused indexes, wasting disk space

First Introduced In

SQL-92 (basic), later extended per vendor

Frequently Asked Questions

When should I create an index?

Create an index when a column or column group is heavily used in JOIN, WHERE, or ORDER BY clauses and the table has enough rows that scans are slow.

Does CREATE INDEX slow down inserts?

Yes. Every insert, update, or delete must also update each related index, so heavy write workloads can suffer. Measure the trade-off before adding indexes.

How do I see if my query uses an index?

Run an EXPLAIN or EXPLAIN ANALYZE on the query. The execution plan will show index scans or seeks if the optimizer chooses the index.

Can I rename or move an index?

Most databases support ALTER INDEX to rename, rebuild, or relocate indexes without dropping and recreating them.