CodeToLive

SQL Indexes & Optimization

Indexes are special lookup tables that database search engines use to speed up data retrieval. Proper indexing is crucial for optimizing query performance in large databases.

Sample Table for Examples

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock_quantity INT,
    date_added DATE,
    supplier_id INT
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(12,2),
    status VARCHAR(20)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL
);
        

Basic Index Types

Creating Indexes

-- Single column index
CREATE INDEX idx_product_name ON products(product_name);

-- Composite index
CREATE INDEX idx_category_price ON products(category, price);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON customers(email);

-- Partial index (PostgreSQL)
CREATE INDEX idx_active_products ON products(product_name)
WHERE stock_quantity > 0;
        

When to Use Indexes

• Columns frequently used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY clauses
• Columns with high selectivity (many unique values)

When to Avoid Indexes

• Very small tables
• Columns frequently updated
• Columns with low selectivity (few unique values)
• Tables with heavy write operations

Query Optimization Techniques

EXPLAIN Statement

-- Analyze query execution plan
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

-- MySQL extended format
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 100;
        

Optimizing JOINs

-- Ensure join columns are indexed
CREATE INDEX idx_order_customer ON orders(customer_id);
CREATE INDEX idx_item_product ON order_items(product_id);

-- Optimized query
SELECT o.order_id, o.order_date, p.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.customer_id = 100;
        

Covering Indexes

-- Index that includes all columns needed by a query
CREATE INDEX idx_covering ON products(category, price, product_name);

-- Query can be satisfied by index alone
SELECT product_name, price 
FROM products 
WHERE category = 'Electronics';
        

Index Maintenance

-- Rebuild index (MySQL)
ALTER TABLE products REBUILD INDEX idx_product_name;

-- Reorganize index (SQL Server)
ALTER INDEX idx_product_name ON products REORGANIZE;

-- Update statistics (PostgreSQL)
ANALYZE products;

-- Drop index
DROP INDEX idx_product_name ON products;
        

Database-Specific Optimization

MySQL Optimization

-- Force index usage
SELECT * FROM products FORCE INDEX (idx_category_price) 
WHERE category = 'Electronics';

-- Optimizer hints
SELECT /*+ INDEX(products idx_category_price) */ * 
FROM products 
WHERE category = 'Electronics';
        

PostgreSQL Optimization

-- Create index concurrently (non-blocking)
CREATE INDEX CONCURRENTLY idx_product_name ON products(product_name);

-- Partial indexes
CREATE INDEX idx_expensive_products ON products(product_name)
WHERE price > 1000;
        

SQL Server Optimization

-- Include additional columns in index
CREATE INDEX idx_category_include ON products(category)
INCLUDE (product_name, price);

-- Filtered index
CREATE INDEX idx_active_products ON products(product_name)
WHERE stock_quantity > 0;
        

Common Performance Issues

Advanced Optimization Techniques

Partitioning

-- Range partitioning by date (MySQL)
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
        

Materialized Views

-- PostgreSQL example
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS total_sales,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Refresh periodically
REFRESH MATERIALIZED VIEW monthly_sales;
        

Query Rewriting

-- Instead of:
SELECT * FROM products WHERE price > 100 OR category = 'Premium';

-- Better:
SELECT * FROM products WHERE price > 100
UNION
SELECT * FROM products WHERE category = 'Premium';
        

Monitoring Tools

• MySQL: PERFORMANCE_SCHEMA, SHOW PROFILE
• PostgreSQL: pg_stat_statements, EXPLAIN ANALYZE
• SQL Server: Query Store, Execution Plans
• Oracle: AWR Reports, SQL Tuning Advisor