MySQL Query Optimization Guide

A query that works correctly is only half the battle -- it also needs to be fast. MySQL query optimization is the process of reducing the resources (CPU, memory, disk I/O) a query consumes. The primary tool for this isEXPLAIN, which reveals how MySQL plans to execute your query before it runs.

Using EXPLAIN

Prefix any SELECT with EXPLAIN to see the execution plan. The output shows which indexes are used, the join order, and the estimated number of rows scanned. EXPLAIN ANALYZE (MySQL 8.0.18+) also shows actual execution times.

EXPLAIN
SELECT o.order_id, c.first_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2025-01-01'
ORDER BY o.total DESC;

-- With actual runtime stats
EXPLAIN ANALYZE
SELECT * FROM products WHERE price > 100;

Reading the EXPLAIN Output

Key columns to watch: type shows the join type (const, eq_ref, ref, range, ALL -- from best to worst). rows estimates how many rows MySQL will examine. Extra reveals operations like "Using filesort" or "Using temporary" that signal potential performance problems.

-- type: ALL means full table scan (bad on large tables)
-- type: ref means index lookup (good)
-- type: const means single-row lookup by primary key (best)

-- "Using filesort" = MySQL sorts results without an index
-- "Using temporary" = MySQL creates a temp table (expensive)

Adding the Right Indexes

The most impactful optimization is usually adding an index. Focus on columns in WHERE, JOIN, ORDER BY, and GROUP BY clauses. Composite indexes should list columns in the order they appear in your queries.

-- Before: full table scan on orders
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2025-01-01';

-- Add a composite index
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

-- After: range scan using the index

Avoiding Common Pitfalls

Certain patterns prevent MySQL from using indexes. Wrapping an indexed column in a function, usingOR across different columns, and leading wildcards in LIKE all force full scans.

-- Bad: function on column prevents index use
SELECT * FROM employees WHERE YEAR(hire_date) = 2024;

-- Good: rewrite as a range
SELECT * FROM employees
WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01';

-- Bad: leading wildcard
SELECT * FROM products WHERE name LIKE '%widget%';

-- Better: use a FULLTEXT index
SELECT * FROM products WHERE MATCH(name) AGAINST('widget');

Optimizing JOINs

Ensure that join columns on both sides are indexed. Keep the result set small by filtering early with WHERE conditions. Avoid selecting unnecessary columns -- SELECT * fetches more data than needed.

-- Ensure both sides of the join are indexed
CREATE INDEX idx_order_items_order ON order_items (order_id);
CREATE INDEX idx_order_items_product ON order_items (product_id);

-- Select only needed columns
SELECT o.order_id, p.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date > '2025-01-01';

The Slow Query Log

Enable MySQL's slow query log to automatically capture queries exceeding a time threshold. This is the best way to discover performance issues in production.

-- Enable slow query log (in my.cnf or dynamically)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Summary

Start every optimization session with EXPLAIN. Add indexes for your most frequent query patterns. Avoid functions on indexed columns. Select only the columns you need. Monitor with the slow query log. Small, targeted changes to indexing and query structure often produce dramatic performance improvements.