Offensive Security Researcher & Quantum Cryptography Analyst

How to Optimize MySQL Database Queries

Abdul Wahab Junaid
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:

ColumnWhat I Look For
typeIdeally const, eq_ref, ref, or range. ALL means a full table scan — a red flag on large tables.
keyWhich index (if any) was actually used
rowsEstimated rows examined — lower is better relative to actual result size
ExtraWatch for Using filesort and Using temporary, both signs of extra work the optimizer had to do

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:

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

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

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

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:

References

Exit mobile version