Creating Indexes in SQL – SQL Tutorial for Beginners

Improving database performance is essential for building fast and scalable applications, and SQL indexes play a crucial role in achieving that. By using the right type of index, you can significantly speed up query execution, reduce database load, and enhance overall efficiency. In this guide, you’ll learn about different types of SQL indexes, how they work, and when to use them effectively in real-world scenarios.

Indexes are used to improve query performance in databases. Different types of indexes serve different purposes depending on your use case.

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.

1.  Without Index → Full table scan (slow)

2. 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

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

Instead of reading every page to find what you need, you go directly to the index, find the topic, and flip to the exact page. SQL indexes work the same way—they help the database find data quickly without scanning every row.
 

Indexes are used to improve query performance in databases. Different types of indexes serve different purposes depending on your use case.

Single-Column Index

A single-column index is created on one column of a table. It helps speed up queries that filter or search based on that specific column.

Example: Searching users by email

Unique Index

A unique index ensures that all values in a column are distinct. It prevents duplicate entries and helps maintain data integrity.

Example: Unique email IDs in a users table

Full-Text Index

A full-text index is used for fast searching within large text fields. It is especially useful for searching words, phrases, or content inside documents.

Example: Searching blog articles by keywords

Clustered Index

A clustered index determines the physical order of data in a table. The table rows are stored in the same order as the index.

Note: A table can have only one clustered index.

Non-Clustered Index

A non-clustered index stores a separate structure with pointers to actual table rows. It does not change the physical order of data.

Note: A table can have multiple non-clustered indexes

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.

Improving database performance is critical for building fast and scalable applications, and SQL indexing plays a key role in achieving that. In this guide, you’ll learn how to speed up queries by up to 100x or more, choose the right index type based on your specific use case, and avoid common indexing mistakes that can negatively impact performance. You’ll also gain a clear understanding of when indexes can actually slow down your database, helping you make smarter optimization decisions for real-world scenarios.