Indexes & Performance

1. What is an Index?

• An index is the index of a book 📖 -> it helps you find the page(row) instead of reading the whole book(table).
• In databases, an index is a data structure (often a B-Tree or Hash) the improves the speed of queries.

2. How Indexes Work

• Without index -> DB does a full table scan (checks every row).
• With index -> DB jumps directly to the matching rows.
• Example :

    Select * from employees where employee_id = '123';

• Without index → scans all employees.
• With index on employee_id → instantly finds the row.

3. Types of Indexes

• Primary Index :
  • Automatically created on the Primary key.
  • Unique + clustered (organizes data physically in the table).
• Unique Index :
  • Ensures all values in a column are unique.
  • Example : email field in Users table.
• Composite Index :
  • Index on multiple columns.
  • Useful for queries filtering multiple fields.
  • CREATE INDEX idx_name_dept ON Employees(name, department_id)
• Full-Text Index :
  • Optimized for searching text (like search engines).
• Hash Index (NoSQL + some SQL engines) :
  • Use hash maps -> very fast for equality lookups (=), not good for ranges.

4. Performance Impact

• ✅ Benefits :
  • Faster SELECT queries (especially for large tables).
  • Reduces I/O by avoiding full table scan.
• ❌ Trade-offs :
  • Slower INSERT, UPDATE, DELETE → index also needs updating.
  • More disk space usage.
  • Wrong/misused indexes can slow queries.

5. SQL vs NoSQL Indexing

• SQL (MySQL, PostgreSQL) → B-Tree, Bitmap, Hash, GiST, etc.
• NoSQL (MongoDB, Cassandra) → Support indexes too (MongoDB uses B-Tree for JSON documents).

⚡Quick Analogy:

• No index → Searching a name in a phonebook by reading every page.
• With index → Jumping directly to the alphabet section.