EXPLAIN Execution Plan Analysis

The EXPLAIN command is the primary tool for diagnosing database performance issues. It reveals how MySQL executes a query, helping identify suboptimal index usage and potential bottlenecks.

EXPLAIN SELECT * FROM users WHERE status = 'active';

Key Output Columns

id: Query identifier showing execution order

select_type: Query type classification

• SIMPLE: Basic SELECT without subqueries or UNION
• PRIMARY: Outermost query in subqueries
• SUBQUERY: First SELECT in a subquery
• DERIVED: SELECT from derived tables (subquery in FROM clause)
• UNION: Subsequent SELECT statements in UNION operations

type: Access method (critical for performance evaluation)

• const/system: Optimized to a single constant row lookup
• eq_ref: Unique index scan for each row combination
• ref: Non-unique index scan returning matching rows
• range: Index range scan using BETWEEN, IN, or comparison operators
• index: Full index scan
• ALL: Full table scan (worst case)

Performance hierarchy: system > const > eq_ref > ref > range > index > ALL

possible_keys: Available indexes MySQL could use

key: Actual index selected by the optimizer

key_len: Length of the index key being used

rows: Estimated rows to examine

Extra: Additional execution information

• Using index: Covering index is used (no table access needed)
• Using filesort: External sorting required
• Using temporary: Temporary table created for query

---

Slow Query Log Configuration

The slow query log records queries exceeding a threshold time, essential for identifying performance problems.

Enabling Slow Query Log

-- Check current configuration
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable at runtime
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_queries.log';
SET GLOBAL long_query_time = 2;

-- Log queries not using indexes
SET GLOBAL log_queries_not_using_indexes = 'ON';

---

Query Profiling

The profiling feature provides detailed execution metrics for query optimization.

-- Enable profiling
SET profiling = 1;

-- Execute your query
SELECT COUNT(*) FROM orders WHERE created_at > '2024-01-01';

-- View all profiled queries
SHOW PROFILES;

-- Get detailed profile for specific query
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

Available metrics: CPU, BLOCK IO, CONTEXT SWITCHES, IPC, MEMORY, PAGE FAULTS, SOURCE, SWAPS

---

SQL Optimization Techniques

Avoid SELECT *

Always specify required columns explicitly:

-- Inefficient
SELECT * FROM customers WHERE country = 'US';

-- Optimized
SELECT customer_id, company_name, email 
FROM customers WHERE country = 'US';

Replace OR with UNION

-- Using OR (slower)
SELECT * FROM products 
WHERE category = 'electronics' OR category = 'appliances';

-- Using UNION (faster)
SELECT * FROM products WHERE category = 'electronics'
UNION ALL
SELECT * FROM products WHERE category = 'appliances';

Avoid Leading Wildcards in LIKE

-- Cannot use index
SELECT * FROM articles WHERE title LIKE '%database%';

-- Can use index
SELECT * FROM articles WHERE title LIKE 'database%';

Avoid Expressions in WHERE Clause

-- Forces full table scan
SELECT id FROM transactions WHERE amount / 2 > 500;

-- Allows index usage
SELECT id FROM transactions WHERE amount > 1000;

Use Numeric Types for Numeric Data

-- Slower: string comparison
SELECT * FROM logs WHERE severity = '3';

-- Faster: numeric comparison  
SELECT * FROM logs WHERE severity = 3;

---

Pagination Optimizaton

Problem with Large Offsets

-- Inefficient for large page numbers
SELECT * FROM entries LIMIT 999900, 100;

Solution: Keyset Pagination

-- Using indexed column for navigation
SELECT * FROM entries 
WHERE id > 999900 
ORDER BY id 
LIMIT 100;

Alternative: Deferred Join

-- First, get only IDs using index
SELECT id FROM entries LIMIT 999900, 100;

-- Then join to get full records
SELECT e.* FROM entries e
INNER JOIN (SELECT id FROM entries LIMIT 999900, 100) AS t
ON e.id = t.id;

---

Index Fundamentals

Indexes enable rapid data retrieval by creating sorted data structures. Without indexes, MySQL must scan every row until finding matches.

B+Tree Structure

MySQL (InnoDB) uses B+Tree indexes, which organize data in a balanced tree format:

• All leaf nodes contain actual data pointers
• Non-leaf nodes store only key values for navigation
• Leaf nodes are linked sequentially for efficient range queries
• B+Tree maintains O(log n) search complexity

Index Advantages

1. Dramatically reduces data retrieval time
2. Enables efficient sorting (ORDER BY)
3. Supports unique constraints
4. Works with any MySQL data type

Index Disadvantages

1. Requires additional storage space
2. Slows down INSERT, UPDATE, DELETE operations
3. Increases maintenance overhead during data changes
4. Not beneficial for low-cardinality columns

---

Index Types

Single-Column Index

-- Regular index
CREATE INDEX idx_lastname ON employees(last_name);

-- Unique index
CREATE UNIQUE INDEX uk_email ON users(email);

-- Primary key (clustered index)
ALTER TABLE products ADD PRIMARY KEY(product_id);

Composite Index

CREATE INDEX idx_order_status_date 
ON orders(customer_id, status, created_at);

Composite indexes follow the leftmost prefix rule—queries must use the leftmost columns to benefit from the index.

Valid for: (customer_id), (customer_id, status), (customer_id, status, created_at) Invalid for: (status), (created_at), (status, created_at)

Covering Index

A covering index contains all columns needed by a query, eliminating table access:

-- This query can be satisfied entirely from the index
SELECT user_id, email, name 
FROM users 
WHERE email = 'user@example.com';

-- Create covering index
CREATE INDEX idx_user_email_covering 
ON users(email, user_id, name);

---

Index Design Guidelines

When to Create Indexes

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

When to Avoid Indexes

• Small tables (full scan may be faster)
• Columns with low cardinality (e.g., boolean flags)
• Frequently updated columns
• Large text or blob columns

General Recommendations

1. Keep index count under 6 per table
2. Use composite indexes instead of multiple single-column indexes when possible
3. Place high-cardinality columns first in composite indexes
4. Consider column order carefully for query patterns
5. Avoid implicit type conversion (causes index bypass)

-- Index won't be used due to type mismatch
SELECT * FROM users WHERE user_id = '123';  -- string vs integer

-- Correct type usage
SELECT * FROM users WHERE user_id = 123;

---

Database Design Best Practices

Table Naming

• Use lowercase with underscores
• Avoid plural forms
• Include business context: payment_transaction, inventory_stock

Field Naming

• Boolean fields: is_active, has_verified
• Use appropriate data types:
  • DECIMAL for financial data (not FLOAT/DOUBLE)
  • CHAR for fixed-length strings
  • VARCHAR for variable-length strings (max 5000)
  • TEXT for large text content (separate table recommended)

Date Storage

Type	Storage	Comparison	Readability
TIMESTAMP	4 bytes	Fast	Requires conversion
DATETIME	8 bytes	Fast	Human readable
BIGINT	8 bytes	Fastest	Requires conversion

For most applications, DATETIME provides the best balance between storage efficiency and usability.