SQL Indexes & Performance Basics Explained with Examples + Practice Questions

When to Use Indexes

Use indexes to speed up data retrieval.

Especially useful for:

• Large tables
• Frequent searches
• JOIN operations

What is an Index

An index is a data structure that helps the database find rows faster, similar to an index in a book.

Without index:

• Database scans entire table (slow)

With index:

• Database jumps directly to required data (fast)

Creating an Index

sql
CREATE INDEX idx_department
ON employees(department);

How It Works

Query:

sql
SELECT * FROM employees
WHERE department = 'IT';

Without index → full table scan
With index → direct lookup

Types of Indexes

1. Single Column Index

sql
CREATE INDEX idx_salary
ON employees(salary);

1. Composite Index

sql
CREATE INDEX idx_dept_salary
ON employees(department, salary);

Used when filtering on multiple columns.

1. Unique Index

sql
CREATE UNIQUE INDEX idx_email
ON employees(email);

Ensures no duplicate values.

Dropping an Index

sql
DROP INDEX idx_department;

When Index is Useful

• WHERE conditions
• JOIN columns
• ORDER BY columns
• Large datasets

When Index is NOT Useful

• Small tables
• Columns with many duplicate values
• Frequently updated columns

Performance Concepts

• Full Table Scan vs Index Scan
• Full scan checks every row
• Index scan uses shortcut

Write vs Read Trade-off

• Index improves read speed
• But slows INSERT / UPDATE / DELETE

Key Concepts

• Index improves query performance
• Works best on frequently searched columns
• Too many indexes can slow down writes
• Balance is important

SQL Performance Tips

• Avoid SELECT *
• Use proper indexes
• Filter early with WHERE
• Use LIMIT when possible
• Avoid unnecessary subqueries

Common Questions (FAQ)

What is an index in SQL

A structure that improves query speed.

Does index improve performance

Yes for reads, but may slow writes.

When should we create index

On frequently searched columns.

Can too many indexes be harmful

Yes, they increase storage and slow updates.

Internal Learning Links

• Learn WHERE filtering
• Learn JOINs
• Learn ORDER BY

Practice CTA

Practice performance-based SQL questions on Schoolabe to write efficient queries.