Optimizing Slow Queries in Production MySQL

Most slow production queries are caused by missing indexes, large table scans, inefficient JOINs, or lock contention. The fastest way to find the problem is EXPLAIN ANALYZE. The most common fix is adding the right composite index. Always test with production-sized data, not development-sized data.

A query that runs in 50ms in development often takes 5 seconds in production. The pattern is consistent: data volume changes everything. A query that scans 1,000 rows in development scans 10,000,000 rows in production.

The First Step: Identify Slow Queries

Before optimizing anything, find the actual slow queries. Enable the slow query log.

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries taking more than 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Find slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Do not guess which queries are slow. Measure. The slow query log shows you exactly which queries take the longest and how often they run. Pay close attention to the times field, which shows query execution time, and the lock_time field, which reveals how much time the query spent waiting for locks. A query that runs frequently but is individually fast can still be your biggest problem if it adds up to significant total time over the course of a day.

Pattern 1: Missing Composite Index

The most common cause of slow queries is a missing composite index. A single-column index is not enough when your query filters on multiple columns.

-- Slow: Scans 1,000,000 rows
EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending' AND created_at > '2026-01-01';
-- type: ALL, key: NULL, rows: 1000000

-- Add composite index
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);

-- Fast: Uses index
EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending' AND created_at > '2026-01-01';
-- type: range, key: idx_orders_user_status_created, rows: 15

The column order in the index matters. Match the index to your WHERE clause order. For queries that filter on user_id and status, put user_id first if it appears in most queries. Range conditions should come last in a composite index. This is because MySQL can use the index for equality lookups on the leading columns, but once it hits a range condition, it cannot use subsequent columns for filtering. Understanding this ordering rule is the single most important concept for writing effective composite indexes.

Pattern 2: SELECT * on Wide Tables

SELECT * on a table with many columns reads everything. If you only need 3 columns, you are reading 20 columns of data you do not need. Beyond just wasted I/O, selecting extra columns prevents covering index optimizations. A covering index includes all the columns a query needs, so MySQL can answer the query entirely from the index without touching the table data at all. When you select extra columns, that optimization becomes impossible.

-- Slow: Reads all columns
SELECT * FROM users WHERE email = '[email protected]';
-- Reads: id, name, email, bio, avatar_url, settings, created_at, updated_at...

-- Fast: Reads only needed columns
SELECT id, name, email FROM users WHERE email = '[email protected]';
-- If there is an index on email, MySQL can do an index-only scan

Pattern 3: Implicit Type Conversion

MySQL silently converts types in comparisons. This prevents index usage. The conversion happens on the column side, not the value side, which is why adding quotes around a numeric column reference can actually fix the problem. The key takeaway is to always match your application parameter types to the database column types exactly, even if MySQL seems to handle the conversion gracefully.

-- Slow: Index not used (string compared to integer)
SELECT * FROM users WHERE phone = 5551234567;
-- MySQL converts phone to integer, cannot use index

-- Fast: Correct type
SELECT * FROM users WHERE phone = '5551234567';
-- Uses index on phone column

-- Slow: UUID compared as string
SELECT * FROM users WHERE uuid = 'abc-123-def-456';

-- Fast: UUID compared correctly
SELECT * FROM users WHERE uuid = UUID_TO_BIN('abc-123-def-456');

Pattern 4: Functions on Indexed Columns

Applying a function to an indexed column prevents index usage. MySQL cannot use the index because the function changes the values.

-- Slow: Function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- MySQL must scan all rows, apply YEAR(), then filter

-- Fast: Range query uses index
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- Uses index on created_at

Pattern 5: Inefficient Subqueries

Correlated subqueries execute once per row. They can be extremely slow on large tables. The problem is that MySQL cannot optimize them as a single operation. Instead, it runs the inner query for every row returned by the outer query, which multiplies the work exponentially. Converting a correlated subquery into a JOIN or derived table lets MySQL execute the aggregation once and join the results, which is far more efficient.

-- Slow: Correlated subquery
SELECT * FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id);
-- Subquery executes for every order

-- Fast: JOIN with derived table
SELECT o.* FROM orders o
JOIN (
  SELECT user_id, AVG(total) as avg_total
  FROM orders GROUP BY user_id
) avg_orders ON o.user_id = avg_orders.user_id
WHERE o.total > avg_orders.avg_total;
-- Subquery executes once

Pattern 6: Lock Contention

When multiple transactions compete for the same rows, they wait for locks. This causes slowdowns that EXPLAIN cannot show.

-- Find lock waits
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT';

-- Find blocking transactions
SELECT * FROM performance_schema.data_lock_waits;

-- Reduce lock duration
START TRANSACTION;
-- Do the minimum work possible
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Pattern 7: Too Many JOINs

Each JOIN adds complexity. A query with 6 JOINs might be slow even with perfect indexes.

-- Slow: 6 JOINs
SELECT u.name, o.total, p.name, c.name, s.status, py.method
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN shipments s ON o.id = s.order_id
JOIN payments py ON o.id = py.order_id;

-- Consider: Can you denormalize? Can you use a summary table?
-- Can you split into multiple simpler queries?

Using EXPLAIN ANALYZE

EXPLAIN shows estimates. EXPLAIN ANALYZE actually runs the query and shows real execution times. Use it to find the actual bottleneck.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

-- Shows actual execution time at each step:
-- -> Limit: 10 row(s) (actual time=45.2..45.3 rows=10)
--   -> Sort: 10 row(s) (actual time=45.1..45.2 rows=10)
--     -> Group aggregate: (actual time=12.3..44.8 rows=50000)
--       -> Hash join: (actual time=8.2..10.1 rows=500000)
--         -> Table scan on u: (actual time=0.5..2.1 rows=200000)

The Optimization Checklist

1. Run EXPLAIN - Check for ALL scans, missing keys, Using filesort
2. Add missing indexes - Composite indexes for multi-column WHERE clauses
3. Remove SELECT * - Only select columns you need
4. Check type conversions - Ensure parameters match column types
5. Avoid functions on indexed columns - Rewrite as range queries
6. Rewrite correlated subqueries - Use JOINs or derived tables
7. Reduce JOIN count - Consider denormalization for read-heavy workloads
8. Check lock contention - Reduce transaction duration
9. Test with production data - Development data is too small to reveal real problems

Common Production Mistakes

Optimizing Without Measuring First

Developers often jump to adding indexes or rewriting queries without first confirming the query is actually slow in production. A query that runs in 50ms on production hardware might be perfectly fine. Always check the slow query log and EXPLAIN output before optimizing. Premature optimization wastes time and can introduce bugs. A common pattern is spending hours rewriting a query only to discover the real problem was a missing index that a one-line CREATE INDEX statement would have fixed.

Adding Indexes Without Checking Write Load

Every index on a table slows down INSERT, UPDATE, and DELETE operations. If a table receives thousands of writes per second, adding five new indexes can create a write bottleneck. Check the table's write volume before adding indexes. Sometimes a slower read query is acceptable if it avoids degrading write performance.

Testing Optimizations on Development Data

A query that uses an index on 10,000 rows might do a full table scan on 10,000,000 rows because MySQL chooses different execution plans based on table size. Always test optimizations against production-sized data. Use mysqldump to copy production data to a staging environment or generate synthetic data that matches production scale.

Key Takeaways

• Start with the slow query log. Do not guess which queries are slow. The log shows you exactly which queries take the longest and how often they run.
• Composite indexes are the most common fix. A single-column index is not enough when your query filters on multiple columns. Match the index columns to your WHERE clause.
• EXPLAIN ANALYZE shows real execution times. EXPLAIN gives estimates. EXPLAIN ANALYZE actually runs the query and shows where time is spent at each step.
• Lock contention causes slow queries that EXPLAIN cannot detect. If EXPLAIN looks fine but the query is still slow, check for lock waits using performance_schema.
• Test against production-sized data. Development data is too small to trigger the same execution plans and reveal the same bottlenecks.

FAQ

When should I optimize a query?

When it appears in the slow query log, when users complain about slowness, or when EXPLAIN shows a full table scan on a large table. Do not optimize queries that are already fast enough.

How do I test with production-sized data?

Use mysqldump to copy production data to a staging environment. Or use a data generator like Faker or the MySQL employees sample database. The key is having enough rows to trigger the same execution plan.

Should I always add indexes for slow queries?

Almost always. But consider the tradeoff: each index slows down writes. If a table has heavy write load, adding too many indexes can hurt performance. Profile both reads and writes before adding indexes.

How do I prioritize which slow queries to fix first?

Sort the slow query log by total time (execution time multiplied by frequency). A query that takes 200ms and runs 10,000 times per day is a higher priority than a query that takes 5 seconds but runs once per day. Fix the queries that consume the most total database time.