📈 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
