What are Indexes in SQL ? Easy Examples for Freshers & Interviews

by

Imagine you have a big dictionary .
If you want to find the meaning of the word “Cricket”,

  • Without index → You will read page by page (slow).
  • With index → You directly check the index page, find the page number, and jump (fast!).

In SQL, Index = shortcut for the database to find data faster.

Indexes in SQL

What are Indexes?

  • Indexes are like a search shortcut in a table.
  • They speed up SELECT queries but may slow down INSERT/UPDATE/DELETE (because database has to update the index also).
  • Mostly used on columns that are searched or joined often (like Roll No, Mobile Number, Email).

Types of Indexes in SQL:

  1. Primary Index
    • Automatically created on Primary Key.
    • Example: Student Roll No (unique + mandatory).
  2. Unique Index
    • Ensures all values are unique.
    • Example: Aadhaar number → no duplicate allowed.
  3. Composite Index
    • Index on multiple columns together.
    • Example: Search student by (First Name + Last Name).
  4. Clustered Index
    • Rearranges the whole table’s data according to the index.
    • Example: Telephone directory arranged by Name.
    • A table can have only ONE clustered index.
  5. Non-Clustered Index
    • Creates a separate pointer structure (like index at back of a book).
    • Example: Telephone directory index pointing to actual page.
    • A table can have many non-clustered indexes.
  6. Full-Text Index (MySQL specific)
    • For searching sentences or big text.
    • Example: Searching words in an essay or blog article.

Quick Recap for Freshers:

  • Index = Shortcut for searching faster.
  • Clustered = data physically arranged.
  • Non-clustered = separate pointer (like book index).
  • Too many indexes = slow inserts/updates (use carefully).