A Simple Analogy

A database index is like a book's table of contents. Instead of reading every page to find a topic, you look up the page number in the index. Queries without indexes scan every row; indexes let the database jump directly to relevant data.

---

What Is Indexing?

An index is a database structure that improves query speed by creating a sorted reference to data. It trades write performance and storage for faster reads.

---

Why Use Indexes?

• Speed: Queries execute milliseconds instead of seconds
• Search optimization: Find data without full table scans
• Sorting: Fast ORDER BY operations
• Joins: Rapid table matching
• Uniqueness: Enforce unique constraints

---

Types of Indexes

| Type | Use Case | |------|----------| | Primary Key | Unique row identifier (clustered) | | Unique Index | Ensure no duplicates (email, username) | | Composite | Multiple columns (UserId, CreatedDate) | | Full-text | Search text content | | Spatial | Geographic/location queries |

---

SQL Indexing Examples

-- Create single-column index
CREATE INDEX idx_users_email ON Users(Email);

-- Create composite index
CREATE INDEX idx_orders_user_date 
ON Orders(UserId, CreatedDate);

-- Create unique index
CREATE UNIQUE INDEX idx_users_username 
ON Users(Username);

-- Drop index
DROP INDEX idx_users_email;

-- Check query execution plan
EXPLAIN SELECT * FROM Users WHERE Email = 'test@example.com';

---

Query Performance Impact

-- Without index: ~2-5 seconds (full table scan)
SELECT * FROM Orders WHERE CustomerId = 123;

-- With index on CustomerId: ~10 milliseconds
CREATE INDEX idx_orders_customer ON Orders(CustomerId);
SELECT * FROM Orders WHERE CustomerId = 123;

---

Practical Example

-- E-commerce indexes
CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    Name NVARCHAR(255),
    CategoryId INT,
    Price DECIMAL(10, 2),
    CreatedDate DATETIME
);

-- Common queries need these indexes:
CREATE INDEX idx_products_category ON Products(CategoryId);
CREATE INDEX idx_products_price ON Products(Price);
CREATE INDEX idx_products_created ON Products(CreatedDate);

-- Composite index for combined queries
CREATE INDEX idx_products_category_price 
ON Products(CategoryId, Price);

---

When NOT to Index

• Small tables (< 10,000 rows)
• Columns with few unique values
• Columns frequently updated
• Columns rarely used in WHERE clauses

---

Best Practices

1. Index WHERE clauses: Index columns in WHERE conditions
2. Avoid over-indexing: Each index slows inserts/updates
3. Monitor performance: Use EXPLAIN to verify index usage
4. Regular maintenance: Rebuild fragmented indexes
5. Primary keys first: Always index your primary keys

---

Related Concepts to Explore

• Query execution plans
• Index fragmentation and maintenance
• Statistics and query optimization
• Covered indexes
• Partitioning strategies

---

Summary

Indexes are essential for database performance. Strategically index columns used in WHERE clauses, joins, and sorting to achieve dramatic query speedups without changing application code.