MySQL Database Optimization: Speed Up Your Website Queries

The Database Bottleneck

Most dynamic websites spend 60-80% of their response time on database queries. A slow database means a slow website — no amount of frontend optimization can fix that.

Identifying Slow Queries

Enable Slow Query Log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

Analyze with EXPLAIN

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 123 
AND status = 'pending' 
ORDER BY created_at DESC;

Look for:

• type: ALL — full table scan (bad!)
• rows: — number of rows examined
• Extra: Using filesort — sorting without index

Essential Optimization Techniques

1. Add Indexes

-- Single column index
CREATE INDEX idx_customer_id ON orders(customer_id);

-- Composite index (covers the full query)
CREATE INDEX idx_customer_status_date 
ON orders(customer_id, status, created_at DESC);

2. Optimize Queries

-- Bad: SELECT *
SELECT * FROM products WHERE category = 'hosting';

-- Good: Select only needed columns
SELECT id, name, price FROM products WHERE category = 'hosting';

3. Use Connection Pooling

Avoid opening a new database connection for every request.

4. Enable Query Cache

For read-heavy workloads:

SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 67108864; -- 64MB

5. Table Maintenance

-- Optimize fragmented tables
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;

-- Analyze table statistics
ANALYZE TABLE wp_posts;

WordPress-Specific Optimization

Clean Up Post Revisions

DELETE FROM wp_posts WHERE post_type = 'revision';

Remove Transients

DELETE FROM wp_options WHERE option_name LIKE '_transient_%';

Optimize Autoloaded Options

SELECT option_name, LENGTH(option_value) as size 
FROM wp_options 
WHERE autoload = 'yes' 
ORDER BY size DESC 
LIMIT 20;

Monitoring

Set up monitoring to catch issues early:

• MySQL Workbench — visual query analysis
• phpMyAdmin — web-based management
• Percona Monitoring — advanced metrics

Conclusion

Database optimization is often the most impactful performance improvement you can make. Start by identifying slow queries, add proper indexes, and maintain your tables regularly. The results are usually dramatic.