📈 Index in SQL

Last Updated: January 2026

An Index is a database object that improves query performance by allowing faster retrieval of rows.

👉 Think of it like an index in a book — it helps you quickly find information without reading the entire book.

  • Speed up SELECT queries
  • Reduce full table scans
  • Improve sorting (ORDER BY) and filtering (WHERE)
  • Can be unique to prevent duplicate values

Hinglish Tip 🗣: Agar table me search fast karna ho, tab Index create karte hain.

CREATE INDEX Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example: Simple Index

CREATE INDEX idx_emp_name
ON employees (emp_name);
  • 📌 Creates an index on the emp_name column
  • 📌 Queries filtering by emp_name will be faster

UNIQUE INDEX

CREATE UNIQUE INDEX idx_emp_id
ON employees (emp_id);
  • 📌 Ensures no duplicate values in emp_id
  • 📌 Acts like a PRIMARY KEY but can be separate

DROP INDEX

DROP INDEX idx_emp_name;
  • 📌 Removes the index but data remains intact

⚠️ Important Points About Index

  • Index speeds up SELECT but slows down INSERT/UPDATE/DELETE
  • Uses extra storage
  • Multiple columns can be indexed (composite index)
  • Auto-created for PRIMARY KEY and UNIQUE constraints
← PreviousNext →