Skip to content

03_What_is_an_Index.md

Latest commit

 

History

History
63 lines (40 loc) · 2.21 KB

03_What_is_an_Index.md

File metadata and controls

63 lines (40 loc) · 2.21 KB

🚀 Topic 12.3: What is an Index? (B-Tree Overview)

Searching a database without an Index is like looking for a name in a phone book that isn't alphabetized. You have to read every single page from start to finish. This is called a Full Table Scan. An Index is the alphabetized version of that data.

1. Definition

Index: A separate data structure (usually a B-Tree) that stores the values of a column in a specific order, along with a "pointer" to the actual row record on the disk.

2. Why This Concept Exists

  • Speed: Finding a row in a table of 1 Billion rows takes milliseconds with an index, but minutes without one.
  • Optimization: MySQL uses indexes to speed up WHERE filters, JOIN conditions, and ORDER BY sorting.
  • Uniqueness: Unique indexes ensure that no two rows have the same value (like Emails).

3. How It Works (The B-Tree - PRO LEVEL)

MySQL (InnoDB) uses B+ Trees for indexing.

  1. Structure: Imagine a branching tree. The top is the "Root," the middle are "Branches," and the bottom are "Leaves."
  2. Order: Every value in the tree is sorted.
  3. Search Logic (O(log N)): To find ID 500, MySQL starts at the root, sees that 500 is > 100, moves to the right branch, sees that 500 is < 700, and arrives at the leaf containing 500 in just 3-4 steps.

The Cost: An index is not "Free."

  • It consumes Disk Space.
  • It slows down INSERT/UPDATE/DELETE because MySQL has to update the index every time the data changes.

4. Syntax / Implementation

-- Create an Index on the Email column
CREATE INDEX idx_user_email ON Users(Email);

-- Remove an Index
DROP INDEX idx_user_email ON Users;

-- See all indexes on a table
SHOW INDEX FROM Users;

5. Real-Life Examples

The "Needle in a Haystack": Searching for a specific Transaction_ID in a table with 500 million records.

  • Without Index: MySQL reads 500,000,000 rows. (Slow).
  • With Index: MySQL reads ~30 internal index nodes. (Instant).

6. Mini Practice Tasks

  • Task 1: What is the primary benefit of creating an index on a column?
  • Task 2: Name one potential downside of having too many indexes on a single table.