SQLMarrow / Course Academy Experience

SQL & Databases Masterclass Course

Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!

COURSE PROGRESS
0%
0 of 40 Modules Solved
COURSE SYLLABUS
40 Lessons
LESSON WORKSPACEAdvanced

SQL Lesson 23: Deep Dive into Database Indexing

Introduction & Core Concept

In previous lessons, we learned that indexes make queries faster by avoiding full table scans. But how do they actually work under the hood?

An Index is a separate data structure that stores a sorted list of keys and pointers to the physical rows in the database table. Think of it like the index at the back of a book.

According to modern database architecture, there are three primary types of indexes used to speed up data retrieval:

1. B-Tree Index (The Default Standard)

The B-Tree (Balanced Tree) is the most common index type in relational databases (like SQL Server, PostgreSQL, and MySQL).

  • How it works: It organizes data in a tree-like structure where nodes are sorted. This allows the database to find values in logarithmic time.
  • Best for: Exact matches, range queries (e.g., WHERE age > 30), and sorting (ORDER BY).
    • 2. Hash Index

    A Hash Index uses a hash table to map keys to row pointers.

  • How it works: It applies a hash function to the search value to calculate a unique hash value, pointing directly to the row.
  • Best for: Lightning-fast exact match queries (e.g., WHERE id = 101).
  • Limitation: It cannot be used for range queries or sorting because the hashed values are not stored in any logical order.
    • 3. Bitmap Index

    A Bitmap Index uses a grid of bits (0s and 1s) to represent whether a value is present in a row.

  • How it works: For each unique value in a column, it creates a string of bits where each bit corresponds to a row in the table. 1 means the value is present, and 0 means it is not.
  • Best for: Columns with low cardinality (few unique values) like Gender, Is_Active, or Status. They are extremely compact and fast for complex boolean queries (AND/OR).
    • Why & Where We Use It
  • Why We Use It: Understanding these types helps you choose the right tool for the right job. Using a B-Tree for a status column might be overkill, while a Hash index is perfect for a key-value lookup.
  • Where We Use It: Data warehouses (Bitmap), transactional systems (B-Tree), and in-memory databases (Hash).
    • Best Practices: What to Do & What NOT to Do
  • What to Do: Use B-Tree indexes for most general-purpose columns. Use Bitmap indexes in read-heavy analytical environments (Data Warehouses) on low-cardinality columns.
  • What NOT to Do: Avoid using Bitmap indexes in highly transactional (OLTP) systems with frequent updates, as updating a single row can lock the entire bitmap!
    • Syntax & Pro Tips

    Most databases automatically use B-Tree as the default. To specify a type (if supported by your specific DB engine):

    -- Example (Syntax varies by DB engine)
CREATE INDEX idx_user_id ON users USING HASH (id);
    Interactive Sandboxed Terminal (Preloaded DB Schema: SHOPMART)
    SQL Query WorkspaceSQLite v3.45 (WASM Mode)
    QUICK INSERT:
    1