Home / SQL / CREATE INDEX

CREATE INDEX

Overview

The CREATE INDEX statement creates an index on one or more columns in a table. Indexes improve query performance by allowing the database to find data faster without scanning the entire table.

Indexes are essential for optimizing database performance, especially on large tables with frequent queries.

Basic Syntax

CREATE INDEX Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Examples

Example 1: Single Column Index

Index on one column

CREATE INDEX idx_email
ON Customers (email);

Example 2: Composite Index

Index on multiple columns

CREATE INDEX idx_name_email
ON Customers (name, email);

Example 3: Unique Index

Index enforcing uniqueness

CREATE UNIQUE INDEX idx_email_unique
ON Customers (email);

Example 4: Index on Foreign Key

Index foreign key column

CREATE INDEX idx_customer_id
ON Orders (customer_id);

When to Use Indexes

Indexes improve performance for:

WHERE clauses: Filtering on indexed columns
JOIN operations: Columns used in JOINs
ORDER BY: Sorting on indexed columns
Foreign keys: Improve JOIN performance
Frequently queried columns: Columns used often in queries

Types of Indexes

Clustered Index

Determines the physical order of data in a table. A table can have only one clustered index.

Non-Clustered Index

Creates a separate structure pointing to data rows. A table can have multiple non-clustered indexes.

Unique Index

Enforces uniqueness of values in indexed columns.

Creating Index on Multiple Columns

Composite indexes can speed up queries that filter on multiple columns:

Multi-column index

CREATE INDEX idx_category_price
ON Products (category, price);

-- This index helps with:
SELECT * FROM Products 
WHERE category = 'Electronics' AND price < 100;

Viewing Indexes

Check existing indexes on a table:

List indexes

-- MySQL
SHOW INDEXES FROM Customers;

-- SQL Server
SELECT * FROM sys.indexes 
WHERE object_id = OBJECT_ID('Customers');

-- PostgreSQL
SELECT indexname FROM pg_indexes 
WHERE tablename = 'customers';

Dropping Indexes

Remove an index when no longer needed:

Drop index

-- MySQL
DROP INDEX idx_email ON Customers;

-- SQL Server/PostgreSQL
DROP INDEX idx_email;

Index Trade-offs

While indexes improve read performance, they have costs:

Storage space: Indexes consume disk space
Write performance: INSERT/UPDATE/DELETE can be slower
Maintenance: Indexes need maintenance as data changes

Best Practices

Index frequently queried columns: Focus on columns in WHERE clauses
Index foreign keys: Improve JOIN performance
Don't over-index: Too many indexes slow writes
Monitor performance: Remove unused indexes
Consider column selectivity: High selectivity columns benefit more
Use composite indexes wisely: Order matters (most selective first)

Automatic Indexes

Some indexes are created automatically:

Primary keys: Automatically indexed
Unique constraints: Often automatically indexed
Foreign keys: Should be manually indexed for performance

Common Mistakes

Too many indexes: Slows down writes unnecessarily
Indexing low-selectivity columns: Little performance benefit
Forgetting to index foreign keys: Poor JOIN performance
Not monitoring index usage: Keeping unused indexes

Next Steps

Learn about creating virtual tables with Views, or explore Data Types for defining column structures.

← Foreign Key Views →