Chapter 5: Indexes and Performance Optimization

September 28, 2025 · View on GitHub

How Indexes Work

Indexes are data structures that improve query speed by providing quick lookups. Think of them like a book's index – instead of reading every page to find a topic, you look it up in the index.

MySQL primarily uses B-Tree indexes, which maintain sorted data and allow searches, sequential access, insertions, and deletions in logarithmic time.

Creating and Managing Indexes

-- Create index
CREATE INDEX idx_customer_email ON customers(email);

-- Create unique index
CREATE UNIQUE INDEX idx_product_sku ON products(sku);

-- Create composite index
CREATE INDEX idx_order_date_status ON orders(order_date, status);

-- Drop index
DROP INDEX idx_customer_email ON customers;

-- View indexes
SHOW INDEX FROM customers;

Index Types

Primary Key Index

Automatically created, clustered index in InnoDB:

ALTER TABLE users ADD PRIMARY KEY (id);

Unique Index

Enforces uniqueness:

ALTER TABLE users ADD UNIQUE KEY unique_email (email);

Composite Index

Multiple columns, order matters:

-- Can use for: (a), (a,b), (a,b,c)
-- Cannot use for: (b), (c), (b,c)
CREATE INDEX idx_abc ON table_name(a, b, c);

Full-Text Index

For text searching:

CREATE FULLTEXT INDEX idx_content ON articles(title, body);

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL performance' IN BOOLEAN MODE);

Spatial Index

For geographic data:

CREATE SPATIAL INDEX idx_location ON stores(coordinates);

Using EXPLAIN

EXPLAIN shows how MySQL executes queries:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Key columns:

type: Join type (system, const, eq_ref, ref, range, index, ALL)
possible_keys: Indexes MySQL could use
key: Index actually used
rows: Estimated rows examined
Extra: Additional information

EXPLAIN Output Types (Best to Worst)

system/const: Single row
eq_ref: One row per row from previous tables
ref: Multiple rows with same value
range: Range scan on index
index: Full index scan
ALL: Full table scan

Query Optimization Techniques

Use Indexes Effectively

-- Good: Uses index
SELECT * FROM users WHERE email = 'user@example.com';

-- Bad: Can't use index (function on column)
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Good: Range query uses index
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- Bad: Can't use index (OR with different columns)
SELECT * FROM users WHERE email = 'test' OR name = 'test';

-- Good: Use UNION instead
SELECT * FROM users WHERE email = 'test'
UNION
SELECT * FROM users WHERE name = 'test';

Optimize JOIN Operations

-- Ensure foreign keys are indexed
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- Join on indexed columns
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

Limit Result Sets Early

-- Bad: Fetch all, then limit
SELECT * FROM (
    SELECT * FROM large_table
) AS t LIMIT 10;

-- Good: Limit early
SELECT * FROM large_table LIMIT 10;

Avoid SELECT *

-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 1;

-- Good: Only needed columns
SELECT name, email FROM users WHERE id = 1;

Index Optimization Strategies

Covering Indexes

Include all query columns in the index:

-- Query needs id, name, email
SELECT id, name, email FROM users WHERE status = 'active';

-- Covering index
CREATE INDEX idx_status_covering ON users(status, id, name, email);

Prefix Indexes

For long string columns:

-- Index first 10 characters
CREATE INDEX idx_email_prefix ON users(email(10));

Index Cardinality

High cardinality (many unique values) = better index effectiveness:

-- Good: High cardinality
CREATE INDEX idx_user_id ON orders(user_id);

-- Less effective: Low cardinality
CREATE INDEX idx_status ON orders(status);  -- Only a few statuses

Query Cache

MySQL can cache query results:

-- Check if query cache is enabled
SHOW VARIABLES LIKE 'query_cache_type';

-- Cache-friendly query (deterministic)
SELECT * FROM products WHERE category = 'electronics';

-- Not cacheable (non-deterministic)
SELECT * FROM products WHERE created_at > NOW() - INTERVAL 1 DAY;

Analyzing Performance

Slow Query Log

Enable to find problematic queries:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Log queries over 2 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Performance Schema

-- Find slowest queries
SELECT
    digest_text,
    count_star AS exec_count,
    sum_timer_wait/1000000000000 AS total_time_sec,
    avg_timer_wait/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Table Optimization

ANALYZE TABLE

Update index statistics:

ANALYZE TABLE orders;

OPTIMIZE TABLE

Defragment and reclaim space:

OPTIMIZE TABLE orders;

Partitioning

Split large tables:

CREATE TABLE orders_partitioned (
    id INT AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    total DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Connection and Server Tuning

Key Configuration Variables

-- View current settings
SHOW VARIABLES;

-- Important settings
SET GLOBAL max_connections = 200;
SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 1GB
SET GLOBAL query_cache_size = 67108864;  -- 64MB

Connection Pooling

Use connection pools in applications to avoid connection overhead.

Common Performance Problems and Solutions

N+1 Query Problem

-- Bad: One query per customer
SELECT * FROM customers;
-- Then for each customer:
SELECT * FROM orders WHERE customer_id = ?;

-- Good: Single query with JOIN
SELECT c.*, o.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Missing Indexes

-- Identify missing indexes
SELECT
    tables.table_name,
    statistics.column_name,
    statistics.cardinality
FROM information_schema.tables
LEFT JOIN information_schema.statistics
    ON tables.table_name = statistics.table_name
WHERE tables.table_schema = 'your_database'
    AND statistics.index_name IS NULL;

Large Result Sets

-- Bad: Load all at once
SELECT * FROM huge_table;

-- Good: Pagination
SELECT * FROM huge_table LIMIT 1000 OFFSET 0;

Best Practices

Index foreign keys for JOIN performance
Monitor slow queries regularly
Use EXPLAIN before deploying new queries
Keep indexes lean - don't over-index
Update statistics regularly with ANALYZE TABLE
Partition large tables by date or other criteria
Archive old data to keep working sets small
Use appropriate data types - smaller is faster

Indexes are crucial for MySQL performance, but they're not free – they slow writes and use disk space. Understanding how to create effective indexes, analyze query performance, and optimize both queries and server configuration is essential for building performant applications.

Next: Chapter 6: Transactions and Concurrency