1. Index Fundamentals and How They Work
Database indexes are data structures that improve the speed of data retrieval operations. Think of them as a book's index - instead of reading every page to find information, you can jump directly to the relevant sections.
How Indexes Work
Indexes create a separate structure that contains pointers to the actual data rows. When a query is executed, the database engine uses the index to quickly locate the required data without scanning the entire table.
💡 Quick Example
Without index: Scan 1 million rows → 1000ms
With index: Direct access → 1ms
2. Types of Database Indexes
Primary Index
Automatically created for primary keys
Best for: Unique identification, JOIN operations
Secondary Index
Created on non-primary key columns
Best for: WHERE clauses, ORDER BY
Composite Index
Covers multiple columns
Best for: Multi-column WHERE conditions
Unique Index
Ensures data uniqueness
Best for: Data integrity, fast lookups
3. Index Creation Strategies
When to Create Indexes
- Columns frequently used in WHERE clauses
- Columns used in JOIN conditions
- Columns used in ORDER BY statements
- Foreign key columns
Index Creation Examples
-- Single column index
CREATE INDEX idx_customer_email ON customers(email);
-- Composite index
CREATE INDEX idx_order_date_status ON orders(order_date, status);
-- Unique index
CREATE UNIQUE INDEX idx_user_username ON users(username);