SQL INDEX

An INDEX improves the speed of data retrieval on large tables by allowing the database to quickly locate rows. It’s like a book’s table of contents — it doesn’t change the data, just speeds up access.

🔹 Basic Syntax

-- Create an index on a single column
CREATE INDEX idx_employee_name ON employees(name);

-- Create a unique index (no duplicate values allowed)
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- Create a composite index (on multiple columns)
CREATE INDEX idx_emp_dept ON employees(department_id, hire_date);

🔹 Dropping an Index

-- MySQL / PostgreSQL
DROP INDEX idx_employee_name;

-- SQL Server
DROP INDEX idx_employee_name ON employees;

🔹 Best Practices

• Index columns used frequently in WHERE, JOIN, or ORDER BY.
• Avoid indexing columns with high update frequency or low selectivity (like boolean flags).
• Composite indexes should match query column order for maximum efficiency.
• Too many indexes can slow down INSERT, UPDATE, and DELETE operations.

🧠 Quick Recap

💡 Use indexes wisely — they’re powerful for reads, but can slow down writes!