• NULL in SQL means: Missing, unknown, or not applicable value.
• It’s not zero 0, it’s not an empty string ''.
• It literally means “I don’t know” or “value does not exist.”

---

NULL ≠ 0
NULL ≠ ”
NULL = unknown

---

Key Characteristics of NULL

Property	Meaning
NULL means unknown	Absence of any value.
NULL is not equal to anything	Even NULL is not equal to NULL! (NULL <> NULL).
Special comparisons needed	Use IS NULL or IS NOT NULL — not = or !=.
Affects expressions and functions	Most operations on NULL produce NULL. (1 + NULL = NULL)

---

How are NULLs Handled in SQL?

1. Comparison with NULL

You can’t use = or != for NULLs!

✅ Correct:

WHERE column IS NULL

or

WHERE column IS NOT NULL

❌ Wrong:

WHERE column = NULL

(It will always fail — NULL compared to anything is unknown.)

2. NULL in Aggregations

Aggregations usually ignore NULLs:

SELECT AVG(score) FROM Students;

NULL score rows are ignored in the AVG() calculation.

3. NULL in Functions

• You can replace NULLs with a default value using COALESCE():

SELECT COALESCE(name, 'Unknown') FROM Users;

If name is NULL, it shows 'Unknown'.

Or with IFNULL() (in MySQL):

SELECT IFNULL(name, 'Unknown') FROM Users;

4. NULL and UNIQUE Constraint

• Most databases allow one NULL in a column with a UNIQUE constraint:
  • NULL is treated as not equal to other NULLs.

Example:

id	email
1	alice@email.com
2	NULL
3	NULL

✅ Some databases (e.g., PostgreSQL, MySQL) allow this.
❌ SQL Server allows only one NULL if you use a UNIQUE index.

---

5. NULL in ORDER BY

When you sort with ORDER BY, NULLs can:

• Appear first or last depending on the database.
• You can control it explicitly:

ORDER BY column_name ASC NULLS LAST

or

ORDER BY column_name DESC NULLS FIRST

NULL in Expressions Example

x	y	x + y
10	5	15
NULL	5	NULL
10	NULL	NULL
NULL	NULL	NULL

• Any operation involving NULL results in NULL.

---

Three-Valued Logic (3VL) in SQL

When NULL is involved, SQL uses three-valued logic:

Comparison	Result
5 = 5	TRUE
5 = 10	FALSE
5 = NULL	UNKNOWN
NULL = NULL	UNKNOWN

SQL WHERE clauses only select rows where the condition is TRUE — not FALSE, not UNKNOWN.

In Short

Concept	Meaning
NULL	Unknown, missing, or inapplicable value.
Special comparison	Use IS NULL / IS NOT NULL.
Aggregates	NULLs are ignored (e.g., in SUM(), AVG()).
Expressions	Any calculation with NULL → result is NULL.
Three-Valued Logic	NULL introduces an unknown third state.

---

Simple Analogy

• 0 = “I have $0.”
• ” = “I have an empty wallet.”
• NULL = “I don’t even know if I have a wallet.”

What is a UNIQUE Constraint?

A UNIQUE constraint ensures that all values in a column (or a combination of columns) are different — no duplicates are allowed.

• Similar to a PRIMARY KEY, but:
  • A table can have only one primary key.
  • A table can have multiple UNIQUE constraints.
• UNIQUE allows NULL values (depending on the database, usually one NULL is allowed, sometimes more).

Key Properties of UNIQUE Constraint

Property	Explanation
Ensures uniqueness	No two rows can have the same value in the UNIQUE column(s).
Allows multiple constraints	You can have more than one UNIQUE constraint in a table.
Allows NULLs (with some DBs)	Most databases allow one NULL in a UNIQUE column (e.g., MySQL, PostgreSQL).
Can be single or composite	Can apply to one column or multiple columns (composite unique constraint).

---

Example: Single Column UNIQUE

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

email must be unique across all users — no two users can have the same email.

Example: Composite UNIQUE (Multiple Columns)

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    UNIQUE (student_id, course_id)
);

A student can enroll in multiple courses.

A course can have multiple students.

But a student cannot enroll in the same course twice — (student_id, course_id) must be unique together.

What Happens if You Violate a UNIQUE Constraint?

If you try to insert duplicate values:

INSERT INTO Users (user_id, email) VALUES (2, 'alice@example.com'); -- OK
INSERT INTO Users (user_id, email) VALUES (3, 'alice@example.com'); -- ❌ Error: duplicate key

The database throws an error:

ERROR: duplicate key value violates unique constraint

UNIQUE vs PRIMARY KEY

PRIMARY KEY	UNIQUE Constraint
Only one per table	Many UNIQUE constraints allowed
Cannot have NULLs	Can have NULLs (one or more)
Enforces uniqueness + NOT NULL	Enforces uniqueness only

In Short

UNIQUE constraint = No duplicate values allowed in the column(s) — ensures data integrity.

Difference Between WHERE and HAVING

WHERE	HAVING
Filters rows before grouping or aggregation.	Filters groups after aggregation.
Used with SELECT, UPDATE, DELETE	Used only with GROUP BY (aggregated queries).
Cannot use aggregate functions (SUM(), COUNT(), etc.) in WHERE.	Can use aggregate functions in HAVING.
Acts on individual rows.	Acts on grouped rows (results of GROUP BY).

---

How to Think About It

• WHERE → filter rows first.
• GROUP BY → group the filtered rows.
• HAVING → filter the groups after aggregation.

---

Example to Make It Clear

Imagine a table Sales:

sale_id	customer_id	amount
1	101	500
2	101	200
3	102	700
4	103	150

---

WHERE Example (filter rows)

Find sales over $300:

SELECT * FROM Sales
WHERE amount > 300;

Result:

sale_id	customer_id	amount
1	101	500
3	102	700

• WHERE filters individual rows based on amount.

---

HAVING Example (filter groups)

Find customers whose total sales are over $500:

SELECT customer_id, SUM(amount) AS total_sales
FROM Sales
GROUP BY customer_id
HAVING SUM(amount) > 500;

Result:

customer_id	total_sales
101	700
102	700

• HAVING filters groups — customers with total sales over 500.
• SUM(amount) — an aggregate function — can’t be used in WHERE, but can be used in HAVING.

🔑 Key Points

• WHERE filters before aggregation — on rows.
• HAVING filters after aggregation — on groups.
• WHERE can’t use SUM(), COUNT(), etc.
• HAVING can use aggregate functions.

---

Another Analogy

Think of SQL like:

1. WHERE = “Clean the raw ingredients.”
2. GROUP BY = “Group them together.”
3. HAVING = “Throw out the bad groups after cooking.”

Simple Example

Step	What Happens
WHERE amount > 300	Pick rows where amount is more than 300.
GROUP BY customer_id	Group those rows by customer.
HAVING SUM(amount) > 500	Keep only groups where total sales > 500.

There are two main strategies depending on how bad the fragmentation is:

---

1. REORGANIZE the Index (Light Defragmentation)

• What it does:
  • Defragment leaf pages online, without locking the table.
  • Moves rows between pages to compact them.
  • Does NOT rebuild the whole index — just tidies up the scattered data.
• Good for:
  • Low to moderate fragmentation (e.g., 5%–30%).
• Fast, non-blocking (online operation).
• Works on-the-fly — no heavy downtime.

✅ SQL Server Example:

ALTER INDEX IndexName ON TableName REORGANIZE;

✅ PostgreSQL (AUTO-VACUUM / CLUSTER):

• PostgreSQL doesn’t have a direct “reorganize,” but VACUUM reduces dead tuples.
• Or manually re-cluster:

CLUSTER TableName USING IndexName;

2. REBUILD the Index (Heavy Defragmentation)

• What it does:
  • Drops and recreates the index completely.
  • Rebuilds the entire B-tree from scratch — no fragmentation left.
• Good for:
  • High fragmentation (e.g., 30%–100%).
• Slow, blocks writes (unless using ONLINE options).
• Can cause downtime (depending on DB and options).
• Reclaims space — the index is fresh and tight like new.

---

✅ SQL Server Example:

ALTER INDEX IndexName ON TableName REBUILD;

(Optionally with ONLINE = ON to minimize downtime.)

✅ PostgreSQL Example:

REINDEX INDEX IndexName;

✅ MySQL Example (InnoDB):

ALTER TABLE TableName ENGINE = InnoDB;

(This rebuilds the table and its indexes.)

---

3. Manual Techniques

• Drop and recreate the index manually:

DROP INDEX IndexName ON TableName;
CREATE INDEX IndexName ON TableName (column1);

Partition the table:

• Break a huge table into smaller partitions based on a key (e.g., date ranges).
• Each partition has fewer rows = less fragmentation.

Fill Factor Tuning (SQL Server, PostgreSQL):

• Adjust fill factor — the percentage of page space to leave empty on inserts.
• E.g., FILLFACTOR = 80 means 80% full pages, 20% free.
• Reduces page splits (but increases storage).

CREATE INDEX IndexName ON TableName (column1) WITH (FILLFACTOR = 80);

Which to Choose? (Quick Decision Table)

Fragmentation %	Recommended Action
0% – 5%	Do nothing — healthy!
5% – 30%	REORGANIZE the index
> 30%	REBUILD the index

---

Bonus: How to Check Fragmentation

✅ SQL Server Example:

SELECT
    avg_fragmentation_in_percent
FROM
    sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('TableName'), NULL, NULL, 'LIMITED');

✅ PostgreSQL: Use pgstattuple extension:

SELECT * FROM pgstattuple('TableName');

✅ MySQL:

SHOW TABLE STATUS LIKE 'TableName';

(See Data_free and Index_length for signs of fragmentation.)

---

Summary

Fix Method	Good for	Impact
REORGANIZE	Light fragmentation (5–30%)	Fast, online, no blocking
REBUILD	Heavy fragmentation (>30%)	Full rebuild, blocks writes (or online rebuild)
Drop and recreate	Full reset	Useful if rebuild is problematic
Partitioning	Big tables	Prevent fragmentation growth
Fill factor tuning	Prevent future fragmentation	Reduces page splits on insert

---

⚡ Bottom Line

Light fragmentation — REORGANIZE.

Heavy fragmentation — REBUILD.

Plan for growth — use fill factor or partitioning.

1. More Pages to Read

• Fragmentation = each page holds fewer rows (wasted space).
• To find or scan the same number of rows, the database needs to read more pages.
• More pages = more I/O operations.

👉 Disk I/O is the slowest part of most databases!

---

2. Cache Inefficiency (Wasted Memory)

• Databases use memory (buffer pool, page cache) to store pages in RAM.
• Fragmented pages waste memory — more pages are needed for the same data.
• Cache misses happen more often.

👉 RAM is precious — more pages = less effective caching.

---

3. Slower Index Traversal

• In a B-tree/B+ tree:
  • More leaf pages mean longer leaf scans.
  • Even if the tree height stays the same, reading through fragmented leaves is slower.

👉 Especially for range queries (e.g., BETWEEN, ORDER BY).

---

4. Increased Page Splits

• Fragmentation increases the likelihood of future page splits.
• When you insert new data into a fragmented index:
  • Less available space → new splits → more fragmentation.

👉 Fragmentation feeds even more fragmentation — a bad cycle.

5. Higher Storage Consumption

• Fragmented indexes use more disk space than necessary.
• Instead of tightly packed data, you have lots of wasted gaps.

👉 Higher storage costs and backups take longer.

6. Slower Backups and Maintenance

• More fragmented pages → more data to scan during:
  • Backups
  • Index statistics gathering
  • Database consistency checks

👉 Maintenance operations take longer and affect uptime.

In Short

Keystone	Why It’s Bad
More pages to read	More I/O = slower reads and queries.
Cache inefficiency	Wasted RAM space; more cache misses.
Slower index traversal	Scanning fragmented leaves is slower.
Increased page splits	Inserts cause more splits, worsening fragmentation.
Higher storage consumption	Wastes disk space; larger database size.
Slower maintenance	Backups, reindexing, and checks become slower.

---

🔥 Bottom Line

Fragmentation makes everything slower and more expensive — both for day-to-day queries and long-term maintenance.

Fragmentation happens when the physical storage of data (or indexes) on disk becomes disorganized.

• In an ideal world: Data is stored contiguously — nice and neat.
• In reality (especially after lots of INSERTs, UPDATEs, DELETEs): Data gets scattered — small pieces in different places.
• This scattering is called fragmentation.

Analogy 📚

Think of a bookshelf:

• Initially: Books are neatly arranged.
• Over time: You add new books, remove old ones, reorganize — but now books are placed randomly with gaps everywhere.
• When you search for a book, you have to jump around — it’s slower.

Same in a database:

• Reading fragmented data = more disk I/O = slower performance.

Types of Fragmentation

1. Table Fragmentation

• The actual table’s rows are stored non-sequentially.
• Reading a single table scan takes more time because the disk head jumps all over to find the next piece of data.

2. Index Fragmentation

• The B-tree (index structure) becomes unbalanced:
  • Lots of empty space inside pages (internal fragmentation).
  • Pages are no longer physically ordered (external fragmentation).
• Index pages can be half-empty due to deletes and updates.
• The index becomes less efficient — more jumps during a lookup.

How Does Fragmentation Happen?

• INSERT new rows randomly.
• UPDATE rows, and the new value doesn’t fit in place (it gets moved).
• DELETE rows, leaving “holes” or empty spots.

Over time, your once neatly packed pages become:

| Row1 | Row2 | FREE | Row3 | FREE | Row4 | Row5 | FREE |

Why is Fragmentation Bad?

• More Disk I/O:
  • Database has to fetch scattered blocks.
• Slower Reads:
  • Reading sequentially is fast.
  • Jumping around is slow, especially on spinning disks (HDDs).
• Bigger Indexes:
  • More empty space = more pages to scan = slower index traversal.

On SSDs, the performance hit is less dramatic, but still real.

How to Fix Fragmentation?

Databases provide ways to rebuild or reorganize tables and indexes:

• REBUILD INDEX: Drops and recreates the index — removes fragmentation completely.
• REORGANIZE INDEX: Rearranges the index pages to be more efficient without fully dropping it.
• VACUUM (in PostgreSQL) or OPTIMIZE TABLE (in MySQL): Repack the table and remove dead space.

Think of it like defragmenting a hard drive — making all the files contiguous again.

---

Quick Example: SQL Server

Check fragmentation:

SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('YourTableName'), NULL, NULL, 'LIMITED');

Rebuild the index:

ALTER INDEX ALL ON YourTableName REBUILD;

In Short

Fragmentation	What Happens	Why It’s Bad
Data/Indexes are scattered	Rows or index pages not in order	Slower read performance, more I/O

---

Real-world tip:

• OLTP systems (many small updates/deletes) suffer fragmentation faster than read-only systems.
• Monitoring and periodically rebuilding indexes is critical for high-performance databases!

Big Picture: What is an Index Structure?

Most database indexes (like MySQL InnoDB, PostgreSQL B-tree indexes, SQL Server) are stored as a B-tree or B+ tree structure.

Think of it as a tree — like an upside-down real tree:

• The root is at the top — entry point.
• Branches are inside — help guide to the right place.
• Leaves are at the bottom — actual data or pointers to data.

---

Here’s a simpler sketch:

            [ ROOT PAGE ]
                /     \
       [ BRANCH PAGE ] [ BRANCH PAGE ]
         /     \          /      \
 [LEAF PAGE] [LEAF PAGE] [LEAF PAGE] [LEAF PAGE]

Root Page: Entry point for the index.

Branch Pages (Internal Nodes): Hold ranges and point to next levels.

Leaf Pages: Store sorted key values + pointers to actual table rows (or row data).

Example with Data

Let’s say you have a table indexed on customer_id:

• Root Page:
  • Says:
    • If customer_id < 5000, go left.
    • If customer_id ≥ 5000, go right.
• Branch Pages:
  • Further divide the range:
    • 0-2500 → go left
    • 2501-4999 → go right
• Leaf Pages:
  • Actually have customer IDs:

Leaf 1: [100, 200, 300, 400]
Leaf 2: [500, 600, 700, 800]
Leaf 3: [5000, 5200, 5400]
Leaf 4: [6000, 6500, 7000]

Simpler ASCII Tree

             [ 5000 ]
            /        \
    [ 2500 ]         [ 6000 ]
    /      \         /     \
[100-400][500-800][5000-5400][6000-7000]

At the root level, it decides:

• Numbers less than 5000 go to the left.
• Numbers 5000 and higher go to the right.

At the branch level:

• Numbers less than 2500 go further left.
• Numbers between 2500 and 5000 go to the next.

At the leaf level:

• Sorted keys in each leaf.

What’s Inside One Page?

Each Page (like [100, 200, 300, 400]) is one Index Page — a block of memory holding multiple sorted keys.

• In a real DB, a page could be 8 KB, and hold maybe 100–500 keys (depends on size).
• Database reads one whole page at a time — not individual rows!

---

Step-by-Step How Search Works

If you search for customer_id = 5400:

1. Start at the root page — 5400 is ≥ 5000 → go right.
2. At next level (branch page) — 5400 is < 6000 → go left.
3. Reach leaf page [5000, 5200, 5400] — find 5400.

✅ Found — and very fast — because it only reads a few pages, not the whole table!

In Summary

Level	What it holds
Root Page	Entry points (top level keys).
Branch Pages	Midpoints — guide search direction.
Leaf Pages	Actual sorted key values (and row pointers).
Page	A block (8 KB) containing multiple keys.

---

Simple Picture

📚 A book with:

• Table of contents (root).
• Chapters (branches).
• Pages with content (leaves).

An index page is a block of memory/disk storage where part of the index data is stored.

More precisely:

• A page is a fixed-size block of data — typically 8 KB (in systems like SQL Server, PostgreSQL, MySQL with InnoDB).
• When a database creates an index (often a B-tree structure), it stores the index entries inside pages.
• The database doesn’t read individual rows — it reads pages.
• Pages are the units of read/write operations in a database.

How It Works

In a typical B-tree or B+ tree index:

• The root page holds the top level of the index (entry points).
• The intermediate pages hold pointers to child pages.
• The leaf pages contain the actual key values (and often a pointer to the data row — or sometimes the row itself, depending on the database).

🗂️ Visual Sketch:

           [ Root Page ]
               /    \
       [ Page 1 ]  [ Page 2 ]
        /     \       /     \
 [Leaf1] [Leaf2] [Leaf3] [Leaf4]

Each [Page] and [Leaf] here is a page — usually 8 KB.

• Leaf pages are where the actual sorted index entries are.
• Internal pages guide the search, kind of like a phonebook index.

Inside an Index Page

Each page contains:

• Keys (the column values you are indexing).
• Pointers to the next level (for non-leaf pages) or to the row (for leaf pages).
• Metadata (page header, free space info, etc.).

📝 Example inside a leaf page:

[Page Header][ Key1 | Row Pointer ][ Key2 | Row Pointer ][ Key3 | Row Pointer ] ... [Free Space]

Why Are Pages Important?

1. Efficiency:
   • Reading 8 KB of data at once is faster than reading small bits many times.
   • Disk and memory operations are optimized for block (page) reads.
2. I/O Cost:
   • Database performance is often measured by the number of pages it must read (logical reads).
   • More fragmented → more scattered pages → more I/O → slower queries.
3. Index Size:
   • Larger indexes = more pages.
   • Smaller indexes = fewer pages = faster navigation.
4. Page Splits:
   • When a page is full and you insert a new key, the database may have to split the page into two pages — this can cause fragmentation.
   • Many page splits mean the index becomes less efficient.

---

Page Splitting Example

Imagine this:

• Page can hold 4 keys.
• It’s full: [1, 2, 3, 4]
• You insert 5 — no space.

Result:

• Split into two pages: [1, 2] and [3, 4, 5]
• This keeps the tree balanced but creates extra work — and over time causes fragmentation if splits happen often.

---

In Short

Concept	Meaning
Page	A fixed-size block (e.g., 8 KB) for storing index data.
Index Page	A page that holds part of the index (keys + pointers).
Root/Leaf Pages	Root = entry point; Leaf = actual key entries.
Page Split	When a page is full, it splits to keep the tree balanced — but causes fragmentation over time.

---

Simple Analogy

Think of a phone book:

• One page holds a chunk of names and phone numbers.
• If you add too many new names, you have to add more pages and reorganize the index.

1. Slower Write Operations

• INSERT, UPDATE, DELETE operations become slower.
• Why? Every time you modify the table, the database has to:
  • Update the table and
  • Update all related indexes.

Example:

INSERT INTO Customers VALUES (100, 'Dave');

If there’s an index on customer_id, the database must:

• Insert the row.
• Also insert the new key into the index tree (e.g., B-tree) in the correct position.

For large tables with many indexes — writing gets heavier.

2. Increased Storage Usage

• Indexes consume disk space.
• More indexes = more disk space used.
• In large tables, indexes can be as big as the data itself.

Example:

• A table of 10 million rows with 3 indexes might use 3–5x more storage than the raw table.

3. Maintenance Overhead

• If the data changes frequently (lots of updates/deletes), the indexes can become fragmented.
• Fragmentation slows down queries over time.
• Requires rebuilding or reorganizing indexes periodically (REBUILD INDEX, OPTIMIZE TABLE).

4. Choosing Wrong Index Can Hurt Performance

• If the wrong column(s) are indexed or the wrong type of index is used:
  • The optimizer might pick a bad execution plan.
  • You might slow down queries rather than speeding them up.

Example:

• Index on last_name but you search by email — index won’t help.

5. Complexity

• More indexes = more complexity for:
  • Database administrators (DBAs).
  • Developers (need to understand which queries use which indexes).
• Harder to tune performance manually when there are too many indexes.

6. Index Overhead on Bulk Operations

• Bulk loads (like BULK INSERT) or mass updates can be painfully slow if indexes exist.
• Often people drop indexes, load data, and then recreate indexes afterward.

Quick Summary

Disadvantage	Impact
Slows down INSERT/UPDATE/DELETE	Index must be updated on every data change.
Extra storage usage	Indexes take disk space.
Fragmentation	Indexes degrade over time, need maintenance.
Bad index design hurts queries	Poor indexes can slow down, not speed up.
More complexity	Managing many indexes becomes harder.
Slower bulk operations	Large inserts/updates slow down significantly.

Golden Rule

👉 Index what you query, not what you insert!

• Only create indexes that support your frequent queries.
• Be careful not to “over-index” — balance is key!

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)