136 ) Types of indexes

 

1. Normal Index (Standard / B-Tree Index)

• What it is: A standard single-column or multi-column index that speeds up data retrieval by maintaining a sorted structure of values pointing to the table rows.

• When to Create:

  • On columns frequently filtered in WHERE clauses (e.g., email, status).

  • On Foreign Key columns to speed up JOIN operations.

  • When queries require sorting (ORDER BY) or range lookups (BETWEEN, >, <).

• How to Create:

  SQL

  CREATE INDEX idx_users_email ON users (email);
  

2. Bitmap Index

• What it is: An index that uses bit arrays (0s and 1s) for each distinct value in a column, mapping directly to table rows. Highly efficient for read-heavy operations combined with logical operations (AND, OR).

• When to Create:

  • In Data Warehouses (OLAP systems) and analytical databases (commonly supported in Oracle).

  • On columns with low cardinality (very few distinct values relative to the total row count, such as gender, is_active, or country_code with < 100 unique values).

• How to Create:

  SQL

  -- Example (Oracle syntax)
  CREATE BITMAP INDEX idx_emp_gender ON employees (gender);
  

3. Unique & Non-Unique Index

• Unique Index:

  • What it is: Ensures that all values in the indexed column(s) are distinct (no duplicates allowed). Primary keys automatically create a unique index.

  • When to Create: To enforce strict business rules where duplication is prohibited (e.g., username, pan_number, tax_id).

  • How to Create:

    SQL

    CREATE UNIQUE INDEX idx_users_username ON users (username);
    

• Non-Unique Index:

  • What it is: Allows duplicate values in the indexed columns. Built strictly for performance tuning.

  • When to Create: On columns prone to repetition that are frequently searched (e.g., department_id, city).

  • How to Create:

    SQL

    CREATE INDEX idx_orders_city ON orders (city);
    

4. Clustered & Non-Clustered Index

• Clustered Index:

  • What it is: Determines the physical storage order of the data rows on the disk. Because rows can only be sorted in one physical order, a table can have only one clustered index. (In MySQL InnoDB, the Primary Key is automatically the clustered index).

  • When to Create: On sequential columns frequently used for range queries or sorting (e.g., auto-incrementing id or created_at).

  • How to Create (SQL Server / general concept):

    SQL

    CREATE CLUSTERED INDEX idx_clustered_emp_id ON employees (emp_id);
    

• Non-Clustered Index:

  • What it is:* A separate structure from the data rows that contains the sorted index key and a pointer/reference back to the actual data row. A table can have multiple non-clustered indexes.

  • When to Create: To support additional fast-access paths for queries that filter by non-primary key columns.

  • How to Create:

    SQL

    CREATE NONCLUSTERED INDEX idx_nc_emp_lastname ON employees (last_name);
    

5. Partitioned & Non-Partitioned Index

• Partitioned Index:

  • What it is: An index built on a partitioned table where the index elements are split across separate storage segments matching the table’s partitions (can be local or global).

  • When to Create: On very large tables (millions/billions of rows) that are partitioned by date or region, to allow maintenance operations and quick partition-level scans.

  • How to Create:

    SQL

    -- Example structure for a partitioned table index
    CREATE INDEX idx_sales_date ON sales (sale_date) LOCAL;
    

• Non-Partitioned Index:

  • What it is: A single global index structure that spans across all partitions of an underlying table.

  • When to Create: When queries need global uniqueness or global lookups across all table partitions uniformly.

  • How to Create:

    SQL

    CREATE INDEX idx_sales_global_ref ON sales (reference_code);
    

6. Expression-Based Index (Function-Based / Filtered Index)

• What it is: An index built on the result of an expression or function applied to one or more columns (e.g., lowercasing strings, math operations), or limited by a condition.

• When to Create:

  • When queries constantly use functions on columns (e.g., WHERE LOWER(email) = 'test@example.com').

  • To index only a subset of rows (Filtered/Partial Index) to save space and write-overhead.

• How to Create:

  SQL

  -- Function-based index (PostgreSQL / Oracle)
  CREATE INDEX idx_users_lower_email ON users ((LOWER(email)));
  
  -- Partial / Filtered index (PostgreSQL)
  CREATE INDEX idx_active_users ON users (email) WHERE status = 'ACTIVE';
  

7. B-Tree Index

• What it is: The foundational, self-balancing tree structure used as the default index type in nearly all relational databases (PostgreSQL, MySQL, Oracle, SQL Server). It organizes data hierarchically into root, branch, and leaf nodes allowing logarithmic time lookups $O(\log n)$.

• When to Create:

  • Default choice for general-purpose querying, primary/foreign keys, exact matches (=), range lookups (>, <, BETWEEN), and sorting (ORDER BY).

• How to Create:

  SQL

  -- Standard CREATE INDEX defaults to a B-Tree structure in most RDBMS
  CREATE INDEX idx_products_price ON products (price);