6.indexing_performance.md

Database Indexing and Performance Optimization

This lesson covers database indexing and query performance optimization techniques, essential skills for efficient database operations.

Database Indexes

Indexes are special data structures that improve the speed of data retrieval operations in a database. They work similarly to a book's index, providing quick access to specific data without scanning the entire table.

Types of Indexes

1. Single-Column Index
   • Created on one column
   • Useful for queries that filter or sort by that column

-- Create a simple index on LastName
CREATE INDEX idx_members_lastname
ON Members(LastName);

2. Composite Index
   • Created on multiple columns
   • Useful for queries that filter by multiple columns

-- Create a composite index on FirstName and LastName
CREATE INDEX idx_members_fullname
ON Members(FirstName, LastName);

3. Unique Index
   • Ensures no duplicate values in the indexed column(s)
   • Automatically created for PRIMARY KEY and UNIQUE constraints

-- Create a unique index on Email
CREATE UNIQUE INDEX idx_members_email
ON Members(Email);

When to Use Indexes

1. DO Create Indexes When:

   • Column is frequently used in WHERE clauses
   • Column is used in JOIN conditions
   • Column is used in ORDER BY or GROUP BY
   • Column has high selectivity (many unique values)

2. DON'T Create Indexes When:

   • Table is small
   • Column has low selectivity
   • Column is frequently updated
   • Column is rarely used in queries

Using EXPLAIN for Query Analysis

EXPLAIN is a powerful tool that shows how the database executes a query, helping identify performance bottlenecks.

Basic EXPLAIN Usage

-- Basic EXPLAIN syntax
EXPLAIN SELECT * FROM Members WHERE LastName = 'Smith';

Understanding EXPLAIN Output

-- Create sample tables and data
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

CREATE INDEX idx_customers_email ON Customers(Email);

-- Insert sample data
INSERT INTO Customers VALUES
(1, 'John Smith', 'john@example.com'),
(2, 'Jane Doe', 'jane@example.com');

-- Compare queries with and without index
EXPLAIN SELECT * FROM Customers WHERE Email = 'john@example.com';
-- Output shows index scan (efficient)

EXPLAIN SELECT * FROM Customers WHERE Name = 'John Smith';
-- Output shows table scan (less efficient)

Common EXPLAIN Components

1. Scan Types

   • Table Scan (full table scan)
   • Index Scan (using an index)
   • Index Seek (using index to find specific rows)

2. Cost Information

   • Relative cost of the operation
   • Higher numbers indicate more resource usage

Performance Optimization Examples

Example 1: Optimizing a Simple Query

-- Before optimization (no index)
EXPLAIN SELECT *
FROM Books
WHERE PublicationYear > 2000;

-- Create index
CREATE INDEX idx_books_year
ON Books(PublicationYear);

-- After optimization (with index)
EXPLAIN SELECT *
FROM Books
WHERE PublicationYear > 2000;

Example 2: Optimizing a JOIN Query

-- Before optimization
EXPLAIN
SELECT m.FirstName, m.LastName, COUNT(l.LoanID) as LoanCount
FROM Members m
LEFT JOIN Loans l ON m.MemberID = l.MemberID
GROUP BY m.MemberID, m.FirstName, m.LastName;

-- Create index for JOIN
CREATE INDEX idx_loans_member
ON Loans(MemberID);

-- After optimization
EXPLAIN
SELECT m.FirstName, m.LastName, COUNT(l.LoanID) as LoanCount
FROM Members m
LEFT JOIN Loans l ON m.MemberID = l.MemberID
GROUP BY m.MemberID, m.FirstName, m.LastName;

Best Practices for Index Management

1. Regular Maintenance

   • Rebuild or reorganize fragmented indexes
   • Remove unused indexes
   • Monitor index usage

2. Index Design Guidelines

   • Keep indexes narrow (fewer columns)
   • Consider column order in composite indexes
   • Balance between read and write performance

3. Common Pitfalls

   • Over-indexing (too many indexes)
   • Under-indexing (missing important indexes)
   • Wrong column order in composite indexes

Exercise 🔴

-- Create a table for an online store
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10,2),
    Stock INT
);

-- Insert sample data
INSERT INTO Products VALUES
(1, 'Laptop', 'Electronics', 999.99, 50),
(2, 'Smartphone', 'Electronics', 599.99, 100),
(3, 'Headphones', 'Electronics', 99.99, 200),
(4, 'Book', 'Books', 19.99, 500);

-- Tasks:
-- 1. Create appropriate indexes for the following queries:
--    - Find products by category
--    - Find products by price range
--    - Find products with low stock

-- 2. Use EXPLAIN to compare performance before and after creating indexes

-- 3. Write and analyze a query that uses multiple indexes

-- Solutions:

-- 1. Creating appropriate indexes
CREATE INDEX idx_products_category ON Products(Category);
CREATE INDEX idx_products_price ON Products(Price);
CREATE INDEX idx_products_stock ON Products(Stock);

-- 2. Compare performance
EXPLAIN SELECT * FROM Products WHERE Category = 'Electronics';
EXPLAIN SELECT * FROM Products WHERE Price BETWEEN 500 AND 1000;
EXPLAIN SELECT * FROM Products WHERE Stock < 100;

-- 3. Query using multiple conditions
EXPLAIN
SELECT *
FROM Products
WHERE Category = 'Electronics'
  AND Price < 1000
  AND Stock > 0
ORDER BY Price DESC;

This exercise demonstrates:

• Index creation for different query patterns
• Using EXPLAIN to analyze query performance
• Understanding how indexes affect complex queries
• Practical application of indexing concepts