SQL Create Index

An index is a database structure used to speed up the retrieval of data rows from a table. Indexes work similarly to an index at the back of a textbook: instead of scanning every single page (a full table scan), the database engine uses the index to quickly locate specific rows matching a search predicate (WHERE clause) or join condition.

While indexes significantly accelerate SELECT query performance, they introduce maintenance overhead for data modification operations (INSERT, UPDATE, DELETE), as the database engine must keep indexes synchronized whenever underlying table data changes.

Basic Syntax & Examples

Indexes are created using the CREATE INDEX statement.

1. Standard (Non-Clustered) Index

A standard single-column index builds an external lookup structure pointing to physical row locations:

CREATE INDEX idx_customers_lastname
ON Customers (LastName);

2. Composite (Multi-Column) Index

A composite index indexes values across multiple columns in a specified left-to-right order. It is effective for queries filtering or sorting by those columns together:

CREATE INDEX idx_orders_customer_date
ON Orders (CustomerID, OrderDate);

The Leftmost Prefix Rule: A composite index on (A, B) optimizes queries filtering on (A) or (A, B), but provides limited or no benefit for queries filtering on (B) alone.

Types of Indexes

Clustered Index vs. Non-Clustered Index

Index Property	Clustered Index	Non-Clustered Index
Data Storage	Reorganizes and physically stores table rows on disk in indexed order.	Stores an external lookup structure with pointers (`ROWID` or Primary Key) to physical rows.
Limit per Table	Exactly 1 per table (data can only be sorted one way physically).	Multiple per table (typically up to hundreds, though 3–5 is recommended).
Default Creation	Automatically created when defining a `PRIMARY KEY` (in SQL Server and MySQL InnoDB).	Created manually using `CREATE INDEX` or automatically for `UNIQUE` constraints.

Unique Index

A unique index guarantees that no two rows in the indexed column(s) share identical values, combining lookup acceleration with integrity validation:

CREATE UNIQUE INDEX idx_users_email
ON Users (Email);

B-Tree vs. Specialized Index Types

Most relational engines use a balanced tree (B-Tree) structure as their default indexing mechanism. B-Trees keep data sorted and allow search, sequential access, insertions, and deletions in logarithmic time ($O(\log n)$).

                      [ Root Node ]
                        /       \
               [ Branch ]       [ Branch ]
                /      \         /      \
           [ Leaf ]  [ Leaf ]  [ Leaf ]  [ Leaf ]
           (Points directly to table data rows)

Common Specialized Index Types:

Hash Index: Provides $O(1)$ equality lookups (=), but does not support range scans (<, >, BETWEEN). Supported natively in MySQL Memory tables and PostgreSQL.
Full-Text Index: Optimized for complex word and phrase searches inside large text bodies (MATCH ... AGAINST in MySQL, tsvector in PostgreSQL).
Spatial Index: Optimized for geographic data types (GEOMETRY, GEOGRAPHY) using R-Tree structures.

When to Use (and Avoid) Indexes

When to Add an Index:

Columns frequently used in WHERE conditions.
Columns used to join tables in JOIN predicates (such as Foreign Keys).
Columns used in ORDER BY or GROUP BY clauses to avoid runtime sorting operations.

❌ When NOT to Add an Index:

Small Tables: For small tables (e.g., a few hundred rows), a full table scan is faster than performing index lookups.
High-DML Tables: Tables subject to heavy INSERT, UPDATE, or DELETE write traffic.
Low-Cardinality Columns: Columns with very few distinct values (e.g., Gender, IsActive flags).

Dropping an Index

To remove an index when it is no longer needed:

-- PostgreSQL / SQL Server / Oracle
DROP INDEX idx_customers_lastname ON Customers;

-- MySQL / MariaDB
ALTER TABLE Customers
DROP INDEX idx_customers_lastname;