SQL CREATE INDEX statement

SQL Tutorial

Here is a clear, practical, and complete guide to the SQL CREATE INDEX statement, including examples for MySQL, PostgreSQL, SQL Server, and Oracle, plus performance best practices.

 What SQL CREATE INDEX statement Does

An index speeds up data retrieval by creating a separate, optimized data structure (usually a B-tree) pointing to rows in a table.

Indexes improve performance for:

  • WHERE filters

  • JOIN conditions

  • ORDER BY

  • GROUP BY

  • Searching text fields (with special index types)

However, indexes:

  • Slow down INSERT/UPDATE/DELETE

  • Take disk space

 Basic Syntax

 Example: Create Index on a Single Column

Speeds up queries like:

 Composite Index (Multiple Columns)

Useful for queries such as:

Important: Column order matters.

 MySQL Example

Unique index:

Fulltext index (MySQL-specific):

 PostgreSQL Example

Expression index:

GIN index for JSONB:

 SQL Server Example

Include columns (SQL Server feature):

 Oracle Example

Function-based index:

 UNIQUE INDEX

Prevents duplicate values but also acts like a performance index.

 DROP INDEX

MySQL

PostgreSQL

SQL Server

Oracle

 When Should You Create an Index?

 Create an index when a column is:

  • Frequently used in WHERE filters

  • Used in JOIN conditions

  • Used for sorting (ORDER BY)

  • Used for grouping (GROUP BY)

  • A foreign key

  • Frequently searched text (full-text indexes)

Avoid indexing:

  • Low-cardinality columns (e.g., gender, is_active)

  • Very small tables

  • Columns frequently updated with large strings

Index Order Matters in Composite Indexes

For:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Works for:

  • WHERE customer_id = ?

  • WHERE customer_id = ? AND order_date > ?

  • ORDER BY customer_id, order_date

Does not work efficiently for:

  • WHERE order_date = ? (skips first column)

Index Performance Tips

  •  Index foreign key columns
  • Index columns used in JOINs
  •  Avoid creating too many indexes (each slows writes)
  •  Use composite indexes when queries filter on multiple columns
  • Monitor index usage (EXPLAIN, EXPLAIN ANALYZE)
  • Consider partial indexes (PostgreSQL) when data is sparse
  •  Use UNIQUE when appropriate for integrity + performance

You may also like...