How to Optimize MySQL Database Queries

Query optimization is the skill I’ve spent the most years refining, and honestly, I don’t think I’ll ever consider it “finished” — every new dataset size, every new access pattern teaches me something new. In this article, I’m bringing together everything I’ve learned about optimizing MySQL queries, from understanding the optimizer’s decision-making process to real production incidents I’ve resolved.

How the MySQL Optimizer Thinks

Before optimizing anything, I remind myself what the optimizer’s actual job is: given a query, it evaluates multiple possible execution plans (which indexes to use, which order to join tables, whether to use a temporary table, whether to sort in memory or on disk) and picks the one it estimates will be cheapest, based on internal cost calculations and table statistics.

graph TD
    A[SQL Query] --> B[Optimizer Generates Candidate Plans]
    B --> C[Cost Estimation Using Table Statistics]
    C --> D[Cheapest Plan Selected]
    D --> E[Execution Engine Runs Plan]
    E --> F[Storage Engine Fetches Rows]

Because the optimizer relies on statistics, one of the very first things I check when a query behaves unexpectedly is whether those statistics are stale:

ANALYZE TABLE orders;

Step 1: Always Start With EXPLAIN

EXPLAIN SELECT o.order_id, c.full_name, o.order_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

I pay close attention to these columns every time:

Step 2: Using EXPLAIN ANALYZE for Real Execution Data

EXPLAIN alone only shows the estimated plan. Since MySQL 8.0.18, I use EXPLAIN ANALYZE to see actual execution timing:

EXPLAIN ANALYZE
SELECT o.order_id, c.full_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';

This shows real timing per operation in the plan tree, which is invaluable when the optimizer’s row estimates and reality diverge significantly.

Common Query Anti-Patterns I Fix Constantly

1. Functions wrapped around indexed columns

-- Bad: prevents index usage on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Good: allows index range scan
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';

2. Leading wildcard LIKE searches

-- Bad: cannot use a B+Tree index at all
SELECT * FROM customers WHERE email LIKE '%@gmail.com';

-- Better: can use an index if email starts with the search term
SELECT * FROM customers WHERE email LIKE 'ahmad%';

For genuine substring search needs, I use a FULLTEXT index instead of fighting LIKE.

3. SELECT * on wide tables

-- Bad: pulls unnecessary columns, prevents covering index usage
SELECT * FROM products WHERE category_id = 5;

-- Good: matches a covering index exactly
SELECT product_id, name, price FROM products WHERE category_id = 5;

4. Implicit type conversions

-- Bad: comparing a string column to a number can silently prevent index usage
SELECT * FROM customers WHERE phone = 3001234567;

-- Good: matching data types
SELECT * FROM customers WHERE phone = '3001234567';

5. OR conditions across different columns

-- Often forces a full scan since a single index can't satisfy both branches
SELECT * FROM orders WHERE customer_id = 5 OR status = 'pending';

-- Rewritten with UNION, letting each half use its own index
SELECT * FROM orders WHERE customer_id = 5
UNION
SELECT * FROM orders WHERE status = 'pending';

Understanding Using filesort and Using temporary

When I see Using filesort in Extra, it means MySQL had to sort results outside of what an index already provides. When I see Using temporary, it means MySQL created an internal temporary table, often for GROUP BY or DISTINCT queries that can’t be satisfied directly from an index.

CREATE INDEX idx_status_created ON orders(status, created_at);

SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

Because the composite index already stores rows sorted by status then created_at, this query avoids Using filesort entirely — MySQL can walk the index in the correct order directly.

Query Optimization for Joins

I always make sure join columns are indexed on both sides, and I think carefully about join order for complex multi-table queries, though I let the optimizer handle this in most cases:

EXPLAIN SELECT c.full_name, o.order_total, p.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE c.id = 501;

I verify each join step in the EXPLAIN output uses eq_ref or ref rather than ALL, which would indicate a missing index on one of the join columns.

Using the Query Optimizer’s Hints (When Necessary)

Occasionally, I encounter a case where the optimizer picks a suboptimal plan due to skewed data distribution. In these rare cases, I use optimizer hints to nudge it:

SELECT /*+ INDEX(orders idx_status_created) */ *
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;

I treat hints as a last resort though — they’re brittle and can become actively harmful if data distribution changes later, so I always re-verify periodically rather than leaving hints in place forever without review.

Caching Strategies I Layer on Top of Query Optimization

Beyond the query itself, I often reduce database load using:

• Application-level caching (Redis/Memcached) for frequently accessed, rarely changing data like product catalogs.
• Materialized summary tables updated via scheduled jobs or triggers for expensive aggregate reports, rather than recomputing them live on every dashboard load.

CREATE TABLE daily_sales_summary (
    summary_date DATE PRIMARY KEY,
    total_orders INT,
    total_revenue DECIMAL(12,2)
);

A Real-World Scenario: Optimizing a Slow Search Feature

A client’s product search endpoint was timing out under moderate load. The original query:

SELECT * FROM products
WHERE name LIKE '%wireless%' OR description LIKE '%wireless%'
ORDER BY created_at DESC;

I diagnosed the issues:

1. Leading wildcard LIKE on both columns prevented any index usage.
2. SELECT * pulled a large description TEXT column unnecessarily for a listing view.
3. ORDER BY created_at without a matching index caused a filesort on every request.

My fix:

ALTER TABLE products ADD FULLTEXT INDEX idx_ft_search (name, description);

SELECT product_id, name, price, created_at
FROM products
WHERE MATCH(name, description) AGAINST('wireless' IN NATURAL LANGUAGE MODE)
ORDER BY created_at DESC
LIMIT 20;

Response time dropped from roughly 4.2 seconds to under 60 milliseconds under the same load, purely from restructuring the query and adding the right index type for the actual access pattern.

Server-Level Configuration Tuning

Beyond individual queries, I tune several server variables that affect overall query performance:

innodb_buffer_pool_size = 8G
innodb_io_capacity = 2000
innodb_flush_log_at_trx_commit = 1
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M

• innodb_buffer_pool_size is the single highest-impact setting — I size it to roughly 60-70% of available RAM on a dedicated database server, since a larger buffer pool means more data can be served from memory instead of disk.
• tmp_table_size and max_heap_table_size control how large an in-memory temporary table can grow before MySQL spills it to disk — I increase these if I see frequent disk-based temp tables in slow query logs.

Using the Slow Query Log

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

I review this log regularly, and I use pt-query-digest (from the Percona Toolkit) to aggregate and rank the worst-offending query patterns rather than reading raw log entries one by one.

pt-query-digest /var/log/mysql/slow.log

Security Considerations Related to Optimization

• I never sacrifice parameterized queries for performance — SQL injection risk is never an acceptable tradeoff for speed.
• I’m cautious with overly permissive caching of query results that might include per-user sensitive data, ensuring cache keys are properly scoped per user/tenant.
• I restrict access to the slow query log and performance schema tables, since they can reveal query patterns and occasionally sensitive literal values.

Troubleshooting Checklist I Follow for Any Slow Query

1. Run EXPLAIN (and EXPLAIN ANALYZE if available) first.
2. Check whether relevant columns are indexed, and whether the leftmost prefix rule is satisfied for composite indexes.
3. Check for functions wrapping indexed columns in WHERE clauses.
4. Check for Using filesort or Using temporary in Extra.
5. Run ANALYZE TABLE to refresh statistics if row estimates look wildly wrong.
6. Check server-level buffer pool sizing and disk I/O capacity.
7. Consider caching or a materialized summary table if the query is inherently expensive (like large aggregations) regardless of indexing.

Performance Best Practices Summary

• Index based on actual query patterns, verified with EXPLAIN.
• Avoid functions on indexed columns and leading wildcards in LIKE.
• Select only needed columns, avoiding SELECT *.
• Use covering indexes for read-heavy, high-frequency queries.
• Monitor the slow query log continuously, not just when users complain.
• Size innodb_buffer_pool_size appropriately for the server’s available RAM.

Frequently Asked Questions

Q: What’s the very first thing I should do when a query is slow? A: Run EXPLAIN (or EXPLAIN ANALYZE) before touching anything else — guessing at the cause without seeing the actual execution plan wastes time.

Q: Why would MySQL ignore an index that clearly matches my WHERE clause? A: Common causes include a function wrapping the indexed column, a data type mismatch, stale table statistics, or the optimizer genuinely calculating that a full scan is cheaper for a small table.

Q: Are query hints a good long-term solution? A: I treat them as a temporary, closely-monitored fix rather than a permanent solution, since they can become counterproductive as data distribution changes over time.

Q: How often should I run ANALYZE TABLE? A: I run it after significant bulk data changes, and some environments schedule it periodically (e.g., nightly) for tables with heavy write activity.

Interview Questions I’ve Encountered

1. Walk through your process for diagnosing a slow MySQL query from scratch.
2. What’s the difference between EXPLAIN and EXPLAIN ANALYZE?
3. Why does a leading wildcard in a LIKE clause prevent index usage?
4. What do Using filesort and Using temporary mean in an EXPLAIN plan, and how would you eliminate them?
5. How would you tune innodb_buffer_pool_size for a server with 32GB of RAM dedicated to MySQL?
6. When, if ever, would you use optimizer hints, and what are the risks?

Summary and Key Takeaways

Query optimization is where all my other MySQL knowledge — indexing, storage engine internals, and server configuration — comes together in practice. I never guess at the cause of a slow query; I start with EXPLAIN, verify my assumptions, and only then make a targeted change, whether that’s an index, a query rewrite, or a server-level tuning adjustment.

Key takeaways:

• Always start troubleshooting with EXPLAIN or EXPLAIN ANALYZE.
• Avoid common anti-patterns: functions on indexed columns, leading wildcards, SELECT *.
• Watch for Using filesort and Using temporary as signals for index improvements.
• Tune innodb_buffer_pool_size and related server variables for your actual hardware.
• Use the slow query log and tools like pt-query-digest to prioritize what to fix first.

References

• MySQL 8.0 Reference Manual, Optimization: https://dev.mysql.com/doc/refman/8.0/en/optimization.html
• MySQL EXPLAIN Output Format: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
• MySQL Slow Query Log: https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html
• Percona Toolkit Documentation: https://docs.percona.com/percona-toolkit/