When a database contains only a few hundred rows, searching for data is usually very fast.
But imagine a database containing:
- 10 million customers
- 5 million orders
- 2 million products
- Millions of transactions
- Large employee or application records
Searching through millions of rows every time a user makes a request can become slow.
This is where SQL Indexes become important.
An SQL index helps a database find rows more efficiently without scanning the entire table in many common situations.
For example, if your application frequently searches users by email:
SELECT *
FROM users
WHERE email = 'rahul@example.com';
An index on email can allow the database to locate matching rows much more efficiently than scanning every row, depending on the database, index, data distribution, and query plan.
In this complete guide, you will learn what SQL indexes are, why indexes are important, how indexes work, different types of indexes, CREATE INDEX, DROP INDEX, unique indexes, composite indexes, clustered and non-clustered indexes, indexes with WHERE, ORDER BY, and JOIN, common mistakes, performance considerations, and best practices.
What Is an SQL Index?
An SQL index is a database structure designed to make certain data retrieval operations faster.
You can think of an index like the index at the back of a book.
Imagine you want to find information about:
SQL Indexes
in a 500-page book.
Without an index, you may need to search page by page.
With a book index, you can quickly find the relevant page.
A database index works with a similar idea.
Instead of searching every row in a large table, the database can use an appropriate index to locate relevant rows more efficiently.
Simple Example
Suppose you have a users table:
id | name | email
---|-------|-------------------
1 | Rahul | rahul@example.com
2 | Priya | priya@example.com
3 | Amit | amit@example.com
...
Your application frequently runs:
SELECT *
FROM users
WHERE email = 'priya@example.com';
You can create an index:
CREATE INDEX idx_users_email
ON users(email);
Now the database has an additional structure that can help it search by email.
Why Are SQL Indexes Important?
Indexes can significantly improve the performance of queries that search, join, or sort data.
They are particularly useful for large tables.
Indexes can help with queries involving:
WHEREJOINORDER BYGROUP BY- Unique lookups
- Range searches
- Some aggregate queries
For example:
SELECT *
FROM orders
WHERE customer_id = 1001;
An index on customer_id may make this query much more efficient when the table is large and the index is appropriate.
How Does an SQL Index Work?
The exact implementation depends on the database system and index type.
A common index structure is a B-tree or B-tree-like structure.
Conceptually, instead of checking:
Row 1
Row 2
Row 3
Row 4
Row 5
...
Row 1,000,000
the database can navigate an ordered index structure to narrow down where matching values are located.
For example:
Index
|
-------------------
| | |
A - F G - M N - Z
|
Priya
|
Row location
This is a simplified illustration. Actual index structures and lookup strategies vary by database.
SQL Index Example
Suppose you have:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(150),
city VARCHAR(100)
);
You frequently search customers by city:
SELECT *
FROM customers
WHERE city = 'Jaipur';
You could create:
CREATE INDEX idx_customers_city
ON customers(city);
The database may then use this index when it determines that doing so is more efficient.
Creating an SQL Index
The general syntax is:
CREATE INDEX index_name
ON table_name (column_name);
Example:
CREATE INDEX idx_products_name
ON products(product_name);
Here:
idx_products_name → Index name
products → Table
product_name → Indexed column
Creating an Index on Multiple Columns
You can create an index containing more than one column.
This is called a composite index or multi-column index.
Example:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
This index contains:
customer_id
order_date
It can be useful for queries that filter or sort using these columns in compatible patterns.
Composite Index Example
Consider this query:
SELECT *
FROM orders
WHERE customer_id = 101
ORDER BY order_date DESC;
An index such as:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
may be useful because the query filters by customer_id and orders by order_date.
However, whether the database actually uses the index depends on its optimizer and the table’s data distribution.
The Leftmost Prefix Principle
For many B-tree composite indexes, column order matters.
Consider:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
The index is organized primarily by:
customer_id
and then by:
order_date
Therefore, it is generally more useful for queries involving:
WHERE customer_id = 101
or:
WHERE customer_id = 101
AND order_date >= '2026-01-01'
But an index beginning with customer_id is not automatically the best choice for:
WHERE order_date >= '2026-01-01'
This is one reason column order matters when designing composite indexes.
The exact optimizer behavior can vary between database systems.
Unique Index
A unique index ensures that indexed values are unique.
Example:
CREATE UNIQUE INDEX idx_users_email
ON users(email);
Now duplicate email values are prevented according to the database’s uniqueness rules.
For example:
rahul@example.com
priya@example.com
amit@example.com
are valid.
But inserting another:
rahul@example.com
would violate the uniqueness requirement.
UNIQUE Constraint vs Unique Index
These concepts are related but not identical.
A UNIQUE constraint expresses a data-integrity rule:
email VARCHAR(150) UNIQUE
A unique index creates an index structure with uniqueness enforcement:
CREATE UNIQUE INDEX idx_users_email
ON users(email);
Database systems often implement unique constraints using unique indexes internally, but the exact implementation and behavior depend on the database.
As a general rule:
- Use a
UNIQUEconstraint when your primary goal is expressing a data-integrity requirement. - Use
CREATE UNIQUE INDEXwhen you specifically need a unique index and your database design calls for it.
Primary Key and Indexes
Primary keys are normally backed by an index or index-like structure in relational database systems.
For example:
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100)
);
The database typically creates the necessary structure to efficiently enforce primary-key uniqueness and support lookups.
Therefore, you usually do not need to create another ordinary index on the same primary-key column just for basic primary-key lookups.
Indexes and Foreign Keys
Foreign-key columns are frequently used in joins.
For example:
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
An index on:
orders.customer_id
may improve join performance, especially for large tables.
Example:
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
Some database systems do not automatically create an index on the referencing foreign-key column, so you may need to create one explicitly.
Indexes with WHERE
One of the most common uses of indexes is improving filtering.
Query:
SELECT *
FROM employees
WHERE department_id = 5;
Index:
CREATE INDEX idx_employees_department
ON employees(department_id);
The optimizer may choose this index if it estimates that it will make the query faster.
Indexes with ORDER BY
Indexes can also help with sorting in some situations.
For example:
SELECT *
FROM products
ORDER BY price;
An index on:
CREATE INDEX idx_products_price
ON products(price);
may help the database avoid or reduce sorting work, depending on the database and execution plan.
Indexes with JOIN
Indexes can improve queries that join large tables.
Example:
SELECT
customers.name,
orders.order_id
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
A useful index might be:
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
The primary key index on customers.customer_id will also normally support efficient lookup on the referenced table.
Indexes with GROUP BY
Indexes can sometimes help with grouping.
For example:
SELECT
department_id,
COUNT(*)
FROM employees
GROUP BY department_id;
An index on:
CREATE INDEX idx_employees_department
ON employees(department_id);
may be useful depending on the database’s query plan.
However, indexes do not automatically make every GROUP BY query faster.
Range Queries and Indexes
Indexes are especially useful for many range conditions.
For example:
SELECT *
FROM products
WHERE price BETWEEN 100 AND 500;
An index on price may allow the database to efficiently locate the relevant range.
Example:
CREATE INDEX idx_products_price
ON products(price);
Other range conditions include:
WHERE price > 1000
and:
WHERE order_date >= '2026-01-01'
Indexes and LIKE
Indexes can sometimes help with LIKE, but the pattern matters.
For example:
SELECT *
FROM customers
WHERE name LIKE 'Rah%';
A normal B-tree index on name may be usable in many database systems because the search has a known prefix.
However:
SELECT *
FROM customers
WHERE name LIKE '%Rah%';
is much harder for a traditional B-tree index to optimize because the pattern begins with a wildcard.
For substring searches, database-specific indexes such as full-text or trigram indexes may be more appropriate.
Clustered Index
A clustered index determines how table data is physically or logically organized around an index key, depending on the database implementation.
SQL Server commonly distinguishes between:
- Clustered indexes
- Non-clustered indexes
A table can have only one clustered index because the table’s rows can have only one clustered ordering structure.
Example in SQL Server:
CREATE CLUSTERED INDEX idx_orders_id
ON orders(order_id);
Clustered-index behavior is highly database-specific.
Non-Clustered Index
A non-clustered index is a separate index structure that contains indexed values and references to the corresponding table rows.
Example in SQL Server:
CREATE NONCLUSTERED INDEX idx_orders_customer
ON orders(customer_id);
A table can generally have multiple non-clustered indexes.
Other database systems use different terminology or storage models, so “clustered vs non-clustered” should not be treated as a universal SQL concept.
Covering Index
A covering index contains all the columns needed by a particular query, allowing the database to obtain the required result directly from the index in some situations.
Suppose you frequently run:
SELECT customer_id, order_date
FROM orders
WHERE customer_id = 1001;
An index containing:
customer_id
order_date
may be able to cover the query.
For example:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
The exact benefits depend on the database’s execution engine.
Partial or Filtered Indexes
Some databases support indexes that cover only a subset of rows.
For example, PostgreSQL supports partial indexes.
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';
This index only contains rows satisfying the condition.
SQL Server provides a related feature called a filtered index.
These features are database-specific and can be very useful for selective data.
Function-Based or Expression Indexes
Suppose you frequently query:
SELECT *
FROM users
WHERE LOWER(email) = 'rahul@example.com';
A normal index on:
email
may not always be sufficient for efficient expression-based lookup.
Some databases support expression or function-based indexes.
For example, PostgreSQL supports:
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));
This can help queries that use the same expression.
The exact syntax and availability depend on the database system.
Full-Text Indexes
Traditional indexes are not always suitable for searching large amounts of natural-language text.
For example:
Product description
Blog content
News articles
Documentation
Database systems may provide full-text search indexes for these workloads.
Examples include database-specific full-text features in:
- MySQL
- SQL Server
- PostgreSQL
The syntax and search capabilities differ between systems.
Spatial Indexes
Applications that work with geographic data may use spatial indexes.
Examples include:
- Maps
- Delivery applications
- Ride-sharing applications
- Location-based services
- Real estate applications
Spatial indexes are designed for geographic or geometric queries.
Their implementation is database-specific.
Indexing Multiple Columns
Suppose you have:
CREATE INDEX idx_products_category_price
ON products(category_id, price);
This can be useful for queries such as:
SELECT *
FROM products
WHERE category_id = 10
AND price < 500;
The order:
category_id → price
is important.
Changing it to:
CREATE INDEX idx_products_price_category
ON products(price, category_id);
creates a different index with potentially different usefulness.
Choosing the Right Index Column
Good candidates for indexes often include columns frequently used in:
WHERE
JOIN
ORDER BY
GROUP BY
Examples:
customer_id
email
order_id
product_id
created_at
status
category_id
But frequency alone is not enough.
You should also consider:
- Selectivity
- Query patterns
- Table size
- Write frequency
- Cardinality
- Existing indexes
- Execution plans
What Is Selectivity?
Selectivity describes how effectively a column can narrow down the number of matching rows.
Consider:
gender
If a table has:
50% Male
50% Female
the column has relatively low selectivity.
Now consider:
email
where almost every value is unique.
This has very high selectivity.
High-selectivity columns are often better candidates for indexes used for equality searches, although the best choice always depends on the query and database optimizer.
High-Cardinality vs Low-Cardinality Columns
Cardinality refers broadly to the number of distinct values in a column.
Example:
email
may have millions of distinct values.
That is high cardinality.
A column such as:
status
might contain only:
active
inactive
pending
That is low cardinality.
A low-cardinality index is not automatically useless. It can still be valuable when combined with other columns, used in a partial/filtered index, or when the data distribution and query make it worthwhile.
Indexes Have a Cost
Indexes make many reads faster, but they are not free.
Every index consumes:
- Storage space
- Memory/cache resources
- Maintenance time
When data changes, indexes may also need to be updated.
For example:
INSERT
UPDATE
DELETE
can require index maintenance.
Therefore:
More indexes do not always mean better performance.
Why Too Many Indexes Can Be a Problem
Suppose a table has:
20 indexes
Every time a row is inserted or modified, many of those indexes may need maintenance.
This can increase:
- Write latency
- Storage usage
- Maintenance overhead
- Backup size
- Memory requirements
Indexes should therefore be created based on real query requirements rather than added randomly.
Indexes and INSERT
Consider:
INSERT INTO users
(name, email)
VALUES
('Rahul', 'rahul@example.com');
If the table has several indexes, the database may need to update each relevant index structure.
Therefore, heavy-write tables require careful index design.
Indexes and UPDATE
Suppose:
UPDATE users
SET email = 'new@example.com'
WHERE user_id = 1;
If email is indexed, the database may need to update the corresponding index entry.
If the updated column is part of multiple indexes, more maintenance may be required.
Indexes and DELETE
For:
DELETE FROM users
WHERE user_id = 1;
the database must remove the corresponding index entries as well.
Therefore, indexes can improve reads but add work to data modifications.
Indexes and Small Tables
Indexes are not always necessary for small tables.
Suppose you have:
countries
with only around 200 rows.
Scanning the entire table may be extremely cheap.
Creating many indexes may add unnecessary overhead.
Always consider the size and access patterns of the table.
Indexes and Large Tables
Indexes become increasingly useful as tables grow, especially for selective queries.
For example:
users
10,000 rows
versus:
users
10,000,000 rows
Searching millions of rows repeatedly can be expensive.
An appropriate index can make a major difference.
EXPLAIN and Index Performance
Do not assume that an index is being used.
Use your database’s execution-plan tools.
For example, many databases support:
EXPLAIN
You might run:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;
The execution plan can show whether the optimizer chooses:
Index Scan
Index Seek
Table Scan
Sequential Scan
The terminology depends on the database.
EXPLAIN ANALYZE
Some databases provide:
EXPLAIN ANALYZE
This can execute the query and provide actual execution information.
For example, PostgreSQL supports:
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1001;
Use such tools carefully on production systems because EXPLAIN ANALYZE may execute the query.
Dropping an Index
If an index is no longer needed, you can remove it.
A common syntax is:
DROP INDEX index_name;
For example:
DROP INDEX idx_products_name;
However, the exact syntax varies between database systems.
For example, SQL Server and PostgreSQL have different details around index removal.
Creating an Index with a Constraint
You may see:
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(150) UNIQUE
);
The database may create the required index structures automatically for these constraints.
You generally should not create redundant indexes on the same columns without a specific reason.
Index Example: E-Commerce Application
Imagine an e-commerce application with:
customers
products
orders
order_items
A common query could be:
SELECT *
FROM orders
WHERE customer_id = 1001
ORDER BY order_date DESC;
A useful index could be:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
Another query:
SELECT *
FROM products
WHERE category_id = 10
AND price BETWEEN 500 AND 2000;
Potentially useful:
CREATE INDEX idx_products_category_price
ON products(category_id, price);
Again, the database optimizer should be used to verify whether these indexes actually improve the workload.
Index Example: User Login
Suppose users log in using email:
SELECT *
FROM users
WHERE email = 'rahul@example.com';
A unique index or unique constraint on email can be appropriate:
CREATE UNIQUE INDEX idx_users_email
ON users(email);
This can both enforce uniqueness and support efficient email lookup.
Index Example: Order History
Suppose an application frequently displays a user’s latest orders:
SELECT *
FROM orders
WHERE customer_id = 1001
ORDER BY order_date DESC;
An index beginning with:
customer_id
and including:
order_date
can be a good candidate:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
The actual performance should be confirmed with the execution plan.
Index Example: Active Records
Suppose your application frequently searches active users:
SELECT *
FROM users
WHERE status = 'active';
If the database supports partial or filtered indexes, you could create a targeted index.
For example, PostgreSQL:
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';
This can be more efficient than indexing all rows when the indexed subset is small and the query pattern is stable.
SQL Index Best Practices
1. Index Frequently Queried Columns
Look at actual application queries.
If a column is frequently used for filtering or joining large tables, consider indexing it.
2. Do Not Index Every Column
Indexes consume storage and increase write overhead.
Create indexes based on actual workload requirements.
3. Consider Composite Indexes
If queries commonly filter using multiple columns:
WHERE customer_id = ?
AND status = ?
a composite index may be more useful than separate indexes in some cases.
4. Put Composite Index Columns in a Useful Order
For example:
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);
Column order affects which query patterns can efficiently use the index.
5. Avoid Redundant Indexes
If you already have:
INDEX(customer_id, order_date)
you may not need another identical index on:
INDEX(customer_id, order_date)
You should also evaluate whether a separate index on customer_id is necessary because the composite index may already support many queries beginning with customer_id.
6. Check Query Plans
Use:
EXPLAIN
or your database’s equivalent.
Do not rely only on assumptions.
7. Monitor Index Usage
Production databases can accumulate unused indexes over time.
Review index usage periodically and remove indexes that are genuinely unnecessary.
8. Consider Write-Heavy Workloads
For systems with many:
INSERT
UPDATE
DELETE
operations, excessive indexes can significantly increase maintenance cost.
Common SQL Index Mistakes
Mistake 1: Creating Too Many Indexes
More indexes can improve some reads but increase write and storage costs.
Mistake 2: Ignoring Column Order
These are different:
CREATE INDEX idx1
ON orders(customer_id, order_date);
and:
CREATE INDEX idx2
ON orders(order_date, customer_id);
Choose the order based on actual query patterns.
Mistake 3: Assuming Every Index Will Be Used
The optimizer chooses the execution strategy.
An index can exist without being selected for a particular query.
Mistake 4: Indexing Low-Selectivity Columns Without a Reason
An index on a column containing only a few values may not be useful for every query.
But low-cardinality indexes can still be useful in certain workloads, especially as part of composite or filtered indexes.
Mistake 5: Ignoring Functions
A query such as:
WHERE LOWER(email) = 'rahul@example.com'
may not efficiently use a normal index on email.
Consider expression/function-based indexes or an appropriate database design when supported.
Mistake 6: Forgetting Foreign-Key Access Patterns
Foreign keys are often involved in joins and lookups.
For large tables, indexing the referencing column can be important.
SQL Indexes Cheat Sheet
| Operation | Example |
|---|---|
| Create index | CREATE INDEX idx_name ON table(column); |
| Create unique index | CREATE UNIQUE INDEX idx_name ON table(column); |
| Composite index | CREATE INDEX idx_name ON table(col1, col2); |
| Remove index | DROP INDEX idx_name; |
| Analyze query | EXPLAIN SELECT ...; |
| Filtered/partial index | Database-specific |
| Function-based index | Database-specific |
| Full-text index | Database-specific |
| Spatial index | Database-specific |
SQL Index vs Constraint
Indexes and constraints are related but have different purposes.
| Feature | Index | Constraint |
|---|---|---|
| Main purpose | Improve access/query performance | Enforce data rules |
| Improves search | Yes | Not its primary purpose |
| Enforces uniqueness | Unique index can | UNIQUE does |
| Enforces relationships | No | FOREIGN KEY does |
| Prevents NULL | No | NOT NULL does |
| Validates values | No | CHECK does |
For example:
email VARCHAR(150) UNIQUE
expresses a data-integrity rule.
While:
CREATE INDEX idx_users_email
ON users(email);
primarily creates an access path for queries.
SQL Index vs Primary Key
A primary key is a constraint that identifies each row uniquely.
An index is a data structure used to improve access and, in some cases, enforce uniqueness.
For example:
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100)
);
The database normally creates the necessary indexing structure to enforce and support the primary key.
Frequently Asked Questions
What is an SQL index?
An SQL index is a database structure that can make certain queries faster by providing an efficient way to locate rows.
Why are indexes used in SQL?
Indexes are primarily used to improve query performance for operations such as filtering, joining, sorting, and searching.
Does an index always make a query faster?
No.
The database optimizer decides whether an index is useful. For small tables or queries returning a large percentage of rows, a table scan may be faster.
Can a table have multiple indexes?
Yes.
A table can have multiple indexes, although too many indexes can increase storage and write overhead.
What is a composite index?
A composite index is an index created using multiple columns.
Example:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
What is a unique index?
A unique index ensures that indexed key values cannot be duplicated according to the database’s uniqueness rules.
What is a clustered index?
A clustered index organizes table storage around an index key in database systems that support this concept, such as SQL Server.
The exact implementation is database-specific.
What is a non-clustered index?
A non-clustered index is a separate index structure that points to the corresponding table rows.
This terminology is especially common in SQL Server.
Should every foreign key have an index?
Not necessarily, but foreign-key columns are often good candidates because they are frequently used in joins, filtering, and referential operations.
The right decision depends on the workload and database system.
Does a primary key automatically create an index?
In most relational database systems, defining a primary key results in an index or equivalent structure being created to enforce uniqueness and support efficient access.
The exact implementation is database-specific.
What is EXPLAIN in SQL?
EXPLAIN displays an execution plan that helps you understand how the database intends to execute a query.
It is useful for analyzing whether indexes are being considered or used.
What is the biggest disadvantage of indexes?
Indexes consume storage and require maintenance when data changes.
Too many indexes can slow down INSERT, UPDATE, and DELETE operations.
Conclusion
SQL indexes are one of the most important tools for improving database performance.
They provide efficient access paths for many common operations and become particularly valuable as tables grow.
The most important concepts to remember are:
- Index → Helps improve data retrieval performance.
- Unique Index → Provides uniqueness enforcement for indexed keys.
- Composite Index → Uses multiple columns.
- Clustered Index → Database-specific organization of table data around an index key.
- Non-Clustered Index → Separate index structure pointing to table rows.
- Partial/Filtered Index → Indexes only selected rows where supported.
- Expression Index → Indexes the result of an expression where supported.
- Full-Text Index → Designed for text-search workloads.
- Spatial Index → Designed for geographic/geometric data.
A basic index looks like:
CREATE INDEX idx_users_email
ON users(email);
A composite index looks like:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
And you can inspect query execution with:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;
The most important lesson is:
Do not create indexes simply because indexes are supposed to make databases faster. Create indexes based on real query patterns, data characteristics, and execution plans.
Once you understand SQL indexes, you are ready to explore more advanced database performance topics such as query optimization, execution plans, database normalization, transactions, CTEs, window functions, and advanced SQL performance tuning.
