Optimize slow SQL queries
Why optimize SQL queries?
Slow SQL queries can significantly affect your application's performance, causing long load times, timeouts, and poor user experience. Optimizing these queries improves speed, reduces server load, and improves scalability.
Identify slow queries
1. Enable slow query log
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add or modify:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
Restart MySQL:
sudo systemctl restart mysql
2. See slow queries
sudo tail -f /var/log/mysql/slow-query.log
3. Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
Optimization techniques
1. Use indexes
Indexes are fundamental to improve query performance.
Create indexes:
-- Simple index
CREATE INDEX idx_email ON users(email);
-- Unique index
CREATE UNIQUE INDEX idx_email_unique ON users(email);
-- Compound index
CREATE INDEX idx_name_age ON users(name, age);
-- Index on multiple columns
CREATE INDEX idx_search ON products(name, category, price);
See existing indexes:
SHOW INDEXES FROM table_name;
Delete unnecessary indexes:
DROP INDEX idx_name ON users;
2. Optimize SELECT queries
Avoid SELECT *:
-- Bad
SELECT * FROM users;
-- Good
SELECT id, name, email FROM users;
Use LIMIT:
-- Limit results
SELECT * FROM products ORDER BY date DESC LIMIT 10;
Use WHERE appropriately:
-- Make sure columns in WHERE have indexes
SELECT * FROM users WHERE email = 'user@example.com';
3. Optimize JOINs
Use indexes on JOIN columns:
-- Make sure JOIN columns have indexes
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.active = 1;
Avoid unnecessary JOINs:
-- If you only need data from one table, don't do JOIN
-- Bad
SELECT u.name FROM users u JOIN posts p ON u.id = p.user_id;
-- Good (if you only need users)
SELECT name FROM users WHERE id IN (SELECT user_id FROM posts);
4. Optimize subqueries
Convert subqueries to JOINs when possible:
-- Less efficient
SELECT * FROM users
WHERE id IN (SELECT user_id FROM posts WHERE active = 1);
-- More efficient
SELECT DISTINCT u.*
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.active = 1;
5. Use UNION instead of OR when possible
-- Less efficient
SELECT * FROM products WHERE category = 'A' OR category = 'B';
-- More efficient
SELECT * FROM products WHERE category = 'A'
UNION
SELECT * FROM products WHERE category = 'B';
6. Optimize ORDER BY
Use indexes for ORDER BY:
-- Create index for sorting
CREATE INDEX idx_date ON posts(date);
-- Query will be faster
SELECT * FROM posts ORDER BY date DESC;
Avoid ORDER BY with functions:
-- Less efficient
SELECT * FROM users ORDER BY UPPER(name);
-- More efficient (if possible)
SELECT * FROM users ORDER BY name;
7. Optimize GROUP BY
Use indexes on GROUP BY columns:
CREATE INDEX idx_category ON products(category);
-- Query will be faster
SELECT category, COUNT(*) FROM products GROUP BY category;
8. Use EXISTS instead of COUNT when possible
-- Less efficient
SELECT * FROM users
WHERE (SELECT COUNT(*) FROM posts WHERE user_id = users.id) > 0;
-- More efficient
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);
9. Avoid functions in WHERE
-- Less efficient (can't use index)
SELECT * FROM users WHERE YEAR(registration_date) = 2024;
-- More efficient
SELECT * FROM users
WHERE registration_date >= '2024-01-01' AND registration_date < '2025-01-01';
10. Use LIMIT with OFFSET efficiently
-- For pagination, consider using cursor-based pagination
-- Instead of:
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 1000;
-- Use:
SELECT * FROM products WHERE id > 1000 ORDER BY id LIMIT 10;
Analysis tools
1. EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
Check especially: - type: Should be ref or eq_ref, not ALL - key: Should show the index used - rows: Number of rows examined (should be low)
2. EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
3. PROFILE
SET PROFILING = 1;
SELECT * FROM users WHERE email = 'user@example.com';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
Optimize tables
1. Analyze tables regularly
ANALYZE TABLE table_name;
2. Optimize tables
OPTIMIZE TABLE table_name;
3. Check and repair tables
CHECK TABLE table_name;
REPAIR TABLE table_name;
MySQL configuration for better performance
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Important parameters:
[mysqld]
# Memory
innodb_buffer_pool_size = 1G
key_buffer_size = 256M
query_cache_size = 64M
# Queries
max_connections = 200
thread_cache_size = 50
table_open_cache = 2000
# Logs
slow_query_log = 1
long_query_time = 2
Restart MySQL after changes.
Best practices
- Use indexes strategically: Don't create indexes on all columns, only on those frequently used in WHERE, JOIN and ORDER BY
- Monitor slow queries: Regularly review the slow query log
- Test changes: Always test optimizations in a development environment first
- Document changes: Keep a record of optimizations made
- Make backups: Before making important changes, make a backup
- Update statistics: Run ANALYZE TABLE regularly
Need help?
If you have slow SQL queries and need help optimizing them, open a ticket from the billing.baires.host panel or contact us for support. Include the problematic query and EXPLAIN result.
You can also reach us through our social media:
- Instagram: @baires_host
- Discord (active support): https://discord.gg/dzjauatAFN
- Linktree: https://linktr.ee/baires.host
