What is an Index in SQL?

An index in a database is like an index in a book:

• Instead of scanning the whole book (all pages), you look at the index to find the page number directly.
• In a database, instead of scanning all rows in a table to find the data, the database uses an index to jump quickly to the right rows.

Technically, an index is a data structure — usually a B-tree (binary search tree) or hash table — that speeds up data retrieval.

---

Why Use an Index?

• Without index: The database does a full table scan — checks every row one by one — which is slow on large tables.
• With index: The database can quickly locate rows without reading all the data — just like finding a word in a dictionary.

Indexes are especially useful for:

• Searching (e.g., WHERE clauses).
• Joining tables.
• Sorting (ORDER BY).
• Enforcing uniqueness (e.g., PRIMARY KEY).

---

Example

Suppose you have a table with millions of customers:

customer_id	name	email
1	Alice	alice@email.com
2	Bob	bob@email.com
…	…	…

If you run:

SELECT * FROM Customers WHERE customer_id = 1234567;

Without index: It checks every row from 1 to 1234567 — slow!

With index on customer_id: It jumps directly to 1234567 using a fast lookup.

How Does an Index Improve Performance?

1. Fast Search:
   • Indexes are usually implemented as B-trees.
   • Finding a row becomes a logarithmic operation O(log n) instead of a linear scan O(n).
2. Quick Joins:
   • If you join tables on indexed columns, the database finds matching rows faster.
3. Speed Up ORDER BY and GROUP BY:
   • If the column used in ORDER BY is indexed, sorting can be much faster.
4. Enforce Constraints:
   • PRIMARY KEY and UNIQUE constraints internally create an index to ensure no duplicates.

Types of Indexes

Type	Purpose
Single-column Index	Indexes one column.
Composite Index	Indexes multiple columns (e.g., (last_name, first_name)).
Unique Index	Ensures all values in a column are unique.
Full-text Index	For fast searches in large text fields.
Spatial Index	For geographic/spatial data (GIS).
Bitmap Index	For columns with few distinct values (OLAP systems).

---

Drawbacks of Indexes

Indexes speed up SELECT, but they can slow down:

• INSERT,
• UPDATE,
• DELETE,
  because the index must also be updated every time you modify the data.

Also:

• More Storage: Indexes use extra disk space.

---

Analogy

• Without index: Reading every page in a book to find “Einstein.”
• With index: Look up “Einstein” in the book’s index — quickly jump to the right page.

---

In Short

With Index	Without Index
Fast retrieval (search, join)	Slow full table scans
Uses more storage	No extra storage
Slower inserts/updates	Faster inserts (but slow selects)