Creating Indexes in SQL

11/22/2025
All Articles

SQL Creating Indexes tutorial with examples for beginners

Creating Indexes in SQL

Creating Indexes in SQL – SQL Tutorial for Beginners

Indexes in SQL are special data structures that improve the speed of data retrieval operations on a table. They work like an index in a book—helping the database find rows faster without scanning the entire table.

In this beginner-friendly SQL tutorial, you will learn:

  • What SQL indexes are

  • How indexes work

  • Types of indexes

  • How to create, view, and delete indexes

  • Index best practices

  • Real-world examples

πŸ”Ή What Is an Index in SQL?

An index is a database object that speeds up data retrieval by creating a quick lookup structure based on one or more columns.

βœ” Without Index → Full table scan (slow)

βœ” With Index → Fast lookup using optimized tree/hash structures

Indexes improve SELECT performance but may slow down:

  • INSERT

  • UPDATE

  • DELETE

Because the index must also be updated.

πŸ”Έ Basic Syntax for Creating an Index

CREATE INDEX index_name
ON table_name (column_name);

πŸ”Ή Example Table: users

id name email city
1 Amit amit@example.com Delhi
2 Neha neha@example.com Mumbai
3 Rahul rahul@example.com Pune

πŸ”Έ Example 1: Create an Index on a Single Column

CREATE INDEX idx_users_city
ON users (city);

This makes city-based search faster:

SELECT * FROM users WHERE city = 'Delhi';

πŸ”Έ Example 2: Create a UNIQUE Index

Ensures no duplicate values.

CREATE UNIQUE INDEX idx_unique_email
ON users (email);

πŸ”Έ Example 3: Create a Composite (Multi-Column) Index

Useful when queries filter by multiple columns.

CREATE INDEX idx_name_city
ON users (name, city);

Speeds up:

SELECT * FROM users WHERE name = 'Amit' AND city = 'Delhi';

πŸ”Ή Types of Indexes in SQL

Index Type Description
Single-column index Index on one column
Composite index Index on two or more columns
Unique index Prevents duplicates
Full-text index Speeds up text searches
Clustered index Sorts table rows physically (SQL Server)
Non-clustered index Logical pointer-based index

πŸ”Έ Example 4: Full-Text Index

CREATE FULLTEXT INDEX idx_article_content
ON articles (content);

Improves performance of:

SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

πŸ”Ή Viewing Indexes in SQL

MySQL:

SHOW INDEXES FROM users;

PostgreSQL:

SELECT * FROM pg_indexes WHERE tablename = 'users';

SQL Server:

EXEC sp_helpindex 'users';

πŸ”Έ Dropping an Index

MySQL:

DROP INDEX idx_users_city ON users;

PostgreSQL:

DROP INDEX idx_users_city;

SQL Server:

DROP INDEX users.idx_users_city;

πŸ”Ή Real-World Use Cases

βœ” Improve search by product name

CREATE INDEX idx_product_name
ON products (name);

βœ” Speed up login using email

CREATE UNIQUE INDEX idx_user_email
ON users (email);

βœ” Speed up filtering orders by user and date

CREATE INDEX idx_user_date
ON orders (user_id, order_date);

πŸ”Ή Best Practices for Indexing

βœ” Index columns used frequently in WHERE, JOIN, ORDER BY
βœ” Use composite indexes when queries use multiple columns
βœ” Avoid indexing small tables (no performance benefit)
βœ” Do not index frequently updated columns
βœ” Avoid too many indexes—slows down write operations
βœ” Use UNIQUE index to enforce data integrity

Summary

In this tutorial on Creating Indexes in SQL, you learned:

  • What indexes are and how they work

  • How to create single-column, composite, and unique indexes

  • How to view and drop indexes

  • Best practices for efficient indexing

Indexes are essential for improving database performance—but must be used wisely.

Article