MySQL Performance Tuning

Key Optimization Areas

CREATE INDEX idx_email
ON users(email);

-- Use LIMIT
SELECT * FROM users
LIMIT 100;

SET GLOBAL 
innodb_buffer_pool_size
= 2G;

SHOW STATUS LIKE
'Slow_queries';

🔹 Creating Indexes

Indexes dramatically speed up data retrieval by creating a sorted reference to your data. Add indexes to columns frequently used in WHERE, JOIN, and ORDER BY clauses. However, too many indexes slow down INSERT and UPDATE operations, so index strategically.

-- Create single column index
CREATE INDEX idx_lastname ON employees(last_name);

-- Create composite index (multiple columns)
CREATE INDEX idx_name ON employees(last_name, first_name);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- View existing indexes
SHOW INDEX FROM employees;

🔹 Using EXPLAIN

EXPLAIN shows how MySQL executes a query, revealing which indexes are used and how tables are scanned. Use it to identify slow queries and optimization opportunities. Look for "Using filesort" or "Using temporary" which indicate performance issues.

-- Analyze query execution plan
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 AND status = 'pending';

-- Extended information
EXPLAIN EXTENDED SELECT * FROM products WHERE price > 100;

-- Format as JSON for detailed analysis
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email LIKE '%@gmail.com';

🔹 Query Optimization Tips

Write efficient queries by selecting only needed columns, using appropriate joins, and avoiding functions on indexed columns. Limit result sets and use WHERE clauses effectively. Small query improvements can dramatically reduce database load and response times.

-- BAD: Select all columns
SELECT * FROM users;

-- GOOD: Select only needed columns
SELECT id, name, email FROM users;

-- BAD: Function on indexed column
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- GOOD: Use range on indexed column
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- Use LIMIT for pagination
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 40;

🔹 Optimizing Joins

Efficient joins are crucial for performance. Ensure join columns are indexed, use appropriate join types, and filter data early with WHERE clauses. Consider the order of joins as MySQL processes them left to right, starting with the smallest result set first.

-- Index foreign keys used in joins
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_product_id ON order_items(product_id);

-- Efficient join with filtering
SELECT o.order_id, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2024-01-01'
AND c.country = 'USA';

🔹 Analyzing Slow Queries

Enable the slow query log to identify problematic queries that take too long to execute. Set a threshold time and MySQL will log queries exceeding it. Review these logs regularly to find optimization opportunities and fix performance bottlenecks.

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking > 2 seconds

-- Check slow query settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- View current running queries
SHOW FULL PROCESSLIST;

-- Kill a slow query
KILL QUERY 12345; -- Replace with actual process ID

🔹 Table Optimization

Regular table maintenance keeps your database running smoothly. OPTIMIZE TABLE reclaims unused space and defragments data. ANALYZE TABLE updates statistics used by the query optimizer. Run these commands during low-traffic periods to avoid performance impact.

-- Optimize table to reclaim space
OPTIMIZE TABLE users;

-- Analyze table to update statistics
ANALYZE TABLE orders;

-- Check table for errors
CHECK TABLE products;

-- Repair corrupted table
REPAIR TABLE customers;

🔹 Caching Strategies

MySQL caches query results and data to reduce disk I/O. The query cache stores identical query results, while the buffer pool caches frequently accessed data pages. Proper cache configuration significantly improves performance for read-heavy applications.

-- Check cache status
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

-- Configure buffer pool (in my.cnf or at runtime)
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB

-- Check buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Flush query cache
FLUSH QUERY CACHE;

🔹 Monitoring Performance

Regular monitoring helps identify performance issues before they become critical. Track key metrics like query execution time, connection counts, cache hit rates, and disk I/O. Use these insights to make informed optimization decisions and capacity planning.

-- Check server status
SHOW STATUS;

-- Monitor specific metrics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Questions';

-- Check table statistics
SHOW TABLE STATUS FROM database_name;

-- View current configuration
SHOW VARIABLES;