Indexes in SQL

What Is an Index?

An index is a special database object that improves the speed of data retrieval from a table. It works like the index in a book: instead of scanning every page, the database can directly locate the required data.

Internally, most databases use B-Tree data structures for indexes, allowing fast searching, sorting, and filtering operations.

---

Why Indexes Are Important

• Improve SELECT query performance
• Speed up WHERE, JOIN, ORDER BY, and GROUP BY operations
• Reduce full table scans
• Essential for large tables with frequent read operations

---

How Indexes Work (Conceptual)

Without index:

• Database scans every row (full table scan)

With index:

• Database looks up index first
• Retrieves only matching row locations
• Much faster for large datasets

---

Creating Indexes

Simple Index (Single Column)

CREATE INDEX idx_last_name ON employees(last_name);

Use Case

• Searches using WHERE last_name = 'Smith'

---

Composite Index (Multiple Columns)

CREATE INDEX idx_dept_salary ON employees(department, salary);

Important Rule

• Index works best when queries filter by:
  • department
  • department AND salary
• Does NOT help much if filtering only by salary

---

Unique Index

CREATE UNIQUE INDEX idx_email ON employees(email);

Benefits

• Enforces uniqueness
• Improves lookup speed
• Prevents duplicate values

---

Indexes and DML Operations

Operation	Effect of Index
SELECT	Faster
INSERT	Slower
UPDATE	Slower
DELETE	Slower

Reason: Indexes must be updated whenever data changes.

---

When to Use Indexes

1. Columns Used in WHERE Clause

CREATE INDEX idx_department ON employees(department);

---

2. Columns Used in JOIN Conditions

CREATE INDEX idx_customer_id ON orders(customer_id);

---

3. Columns Used in ORDER BY

CREATE INDEX idx_order_date ON orders(order_date);

---

4. Foreign Key Columns

CREATE INDEX idx_fk_department ON employees(department_id);

Why?

• Improves JOIN performance
• Speeds up parent-child lookups

---

Viewing Indexes

Show Indexes on a Table

SHOW INDEXES FROM employees;

---

Using Information Schema

SELECT * 
FROM information_schema.statistics
WHERE table_name = 'employees';

---

Dropping Indexes

DROP INDEX idx_last_name ON employees;

---

Types of Indexes (Extra Knowledge)

1. Clustered Index

• Defines physical order of data
• Only one per table
• Primary key usually creates clustered index

2. Non-Clustered Index

• Separate structure pointing to table rows
• Multiple allowed per table

3. Full-Text Index

• Used for text search
• Useful for articles, blogs, descriptions

CREATE FULLTEXT INDEX idx_description ON products(description);

---

4. Hash Index (Database-specific)

• Extremely fast for equality checks
• Not good for range queries

---

Index Cardinality (Very Important)

Cardinality = Number of unique values in a column

Cardinality	Index Effectiveness
High (email, ID)	Excellent
Medium (city)	Good
Low (gender, boolean)	Poor

---

When NOT to Use Indexes

• Very small tables
• Columns with low cardinality
• Columns rarely used in queries
• Tables with heavy INSERT/UPDATE/DELETE
• Temporary tables

---

Common Indexing Mistakes

• Indexing every column
• Indexing low-value columns
• Ignoring composite index order
• Too many indexes hurting performance
• Forgetting to index foreign keys

---

Index Maintenance

• Remove unused indexes
• Rebuild fragmented indexes
• Monitor query performance
• Use EXPLAIN to analyze queries

---

Using EXPLAIN (Performance Analysis)

EXPLAIN SELECT * FROM employees WHERE department = 'IT';

Helps You Understand

• Index usage
• Query execution plan
• Full scan vs index scan

---

Indexes vs Constraints

Feature	Index	Constraint
Purpose	Performance	Data integrity
Enforces rules	❌	✅
Improves speed	✅	❌

---

Real-World Use Cases

• Job portals (search by skill, location)
• E-commerce filtering
• Banking transactions
• CRM and ERP systems
• Analytics dashboards