SQL Index Optimization

Index Optimization

Indexes are one of the most powerful tools for improving SQL query performance.

However, indexes are not magic.

In real-world systems:

Some indexes improve performance drastically

Some indexes are never used

Some indexes actually slow the system down

This is why index optimization is important. It is not about creating more indexes, but about creating the right indexes.

What Does Index Optimization Mean?

Index optimization means:

Designing indexes based on query patterns

Ensuring indexes are actually used by the optimizer

Reducing unnecessary or redundant indexes

Balancing read performance with write cost

The goal is:

Faster queries with minimal overhead

Understanding Covering Indexes

When an Index Alone Can Answer a Query

A covering index is an index that contains all the columns required by a query.

This means:

The database does not need to read the table at all

Data is fetched directly from the index

Query execution becomes much faster

Example: Query Without a Covering Index

SELECT emp_name, salary
FROM employees
WHERE department_id = 10;

If there is an index only on department_id:

Database finds matching rows using the index

But still needs to read the table for emp_name and salary

This is called a lookup.

Example: Creating a Covering Index

CREATE INDEX idx_emp_cover
ON employees(department_id, emp_name, salary);

Now:

All required columns are in the index

No table access is needed

Query becomes significantly faster

Why Covering Indexes Improve Performance

Covering indexes:

Reduce disk I/O

Avoid extra table lookups

Improve cache efficiency

Are especially useful for read-heavy systems

They are commonly used in:

Reporting queries

Dashboards

Frequently executed SELECT statements

Column Order Matters in Indexes

Index column order affects performance.

For an index:

(department_id, emp_name, salary)

Queries filtering by department_id - use index

Queries filtering by emp_name only - may not use index

Rule of thumb:

Put the most selective and frequently filtered column first

Common Indexing Pitfalls

1. Creating Too Many Indexes

Every index:

Speeds up SELECT

Slows down INSERT, UPDATE, DELETE

Too many indexes can:

Increase write latency

Consume extra storage

Complicate maintenance

2. Indexing Low Cardinality Columns

Columns with few unique values (like status or gender) are poor index candidates.

Example:

status → ACTIVE / INACTIVE

The optimizer may ignore such indexes.

3. Using Functions on Indexed Columns

SELECT * FROM employees
WHERE UPPER(emp_name) = 'RAHUL';

Even if emp_name is indexed:

Index may not be used

Full table scan may occur

Instead, normalize data or avoid functions in WHERE clauses.

4. Indexes Not Matching Query Patterns

Creating indexes without understanding queries is ineffective.

Example:

Index on (salary, department_id)

Query filters by department_id only

The index may not be useful.

5. Forgetting Index Maintenance

Over time:

Data changes

Index statistics become outdated

Optimizer makes poor decisions

Regular maintenance is essential.

How to Verify Index Usage

EXPLAIN SELECT ...

Look for:

Index scan instead of table scan

Reduced row estimates

Lower execution cost

Never assume an index is being used.

Index Optimization Strategy

Identify slow queries

Use EXPLAIN to analyze them

Index columns used in WHERE, JOIN, ORDER BY

Consider covering indexes for frequent queries

Avoid unnecessary indexes

Monitor and refine continuously

Indexes Vs Performance Trade off

Aspect	Benefit	Cost
SELECT	Faster	-
INSERT	-	Slower
UPDATE	-	Slower
DELETE	-	Slower
Storage	-	More space

Index optimization is about finding the right balance.

Two Minute Drill

Index optimization improves real performance
Covering indexes conatain all query columns
Covering indexes avoid table lookups
Column order in indexes matters
Too many indexes hurt write performance
Low cardinality columns are poor candidates
Always validate with EXPLAIN