Why Slow MySQL Queries Are a Symptom, Not the Problem

Why Slow Queries Are a Symptom, Not the Problem

A slow MySQL query is rarely just a slow query. It is a signal that something deeper is wrong: a missing index, a poorly planned join, a misconfigured server, or a query that worked fine at 10,000 rows and fell apart at 10 million.

Engineers who fix these issues fastest understand what MySQL is actually doing internally, how it builds execution plans, when it ignores indexes, and what forces it to sort on disk. This guide covers that behaviour and shows you how to act on it.

Why MySQL Queries Become Slow Internally

When you run a query, MySQL's query optimizer builds an execution plan, a step-by-step strategy for retrieving the data. The optimizer estimates the cost of different strategies based on index statistics, table cardinality, and row counts, then selects the strategy it believes is cheapest.

The problems start here. The optimizer is working with estimates, not certainties. Stale statistics, skewed data distributions, or low-cardinality columns can push it toward a plan that looks cheap on paper but is expensive in practice.

Full Table Scans

A full table scan means MySQL reads every row in the table to find matches. On a table with a million rows and no useful index, this is catastrophic. The EXPLAIN output will show type = ALL — the worst possible access type.

Filesort and Temporary Tables

When MySQL cannot satisfy an ORDER BY or GROUP BY using an index, it sorts the data in memory or on disk — a filesort operation. When GROUP BY or complex subqueries require intermediate storage, MySQL creates temporary tables. Both appear in EXPLAIN's Extra column and are expensive signals to watch for.

Cardinality and the Optimizer Trap

Index cardinality is the number of unique values in an indexed column. A column like status with values active and inactive has cardinality of 2 — very low. MySQL often decides a full table scan is cheaper than using such an index, because it estimates it will scan most rows anyway. The optimizer is not wrong, but the result can still surprise developers expecting an index to be used.

How to Detect Slow Queries in Production

Enabling the Slow Query Log

The slow query log is your first diagnostic tool. Enable it and set a threshold — any query taking longer than that value is logged with its execution time, lock time, and rows examined.

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 1;  -- log queries over 1 second

SET GLOBAL log_queries_not_using_indexes = 'ON';

The rows_examined value in each log entry is particularly useful. A query returning 50 rows but examining 500,000 is doing far more work than it should — a clear index problem.

Using performance_schema

For production systems where you cannot enable file logging freely, performance_schema provides query-level statistics without restarting the server. The events_statements_summary_by_digest table is your starting point — it groups similar queries and shows average execution time, total rows examined, and execution count.

SELECT digest_text, avg_timer_wait/1e12 AS avg_sec,

       count_star, sum_rows_examined

FROM performance_schema.events_statements_summary_by_digest

ORDER BY avg_timer_wait DESC LIMIT 10;

Reading EXPLAIN and EXPLAIN ANALYZE

EXPLAIN shows you the execution plan MySQL has chosen before running the query. EXPLAIN ANALYZE actually runs it and returns both estimated and actual costs — far more useful for diagnosing real performance.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

The key columns to focus on:

EXPLAIN ANALYZE adds actual row counts and loop times after each estimated value. When estimated rows = 1 but actual rows = 50,000, the optimizer was working with bad statistics. Running ANALYZE TABLE refreshes them.

Common Causes of MySQL Query Slowness

Missing or Unused Indexes

The most frequent cause by far. A query on orders.customer_id that runs a full table scan means either the column has no index, or MySQL has decided the index is not selective enough to use. Check with EXPLAIN, then add or adjust indexes accordingly.

SELECT * in Production Code

Fetching every column forces MySQL to read full rows even when only two columns are needed. It also prevents covering indexes from working. Always specify the columns you actually need.

Functions on Indexed Columns

Wrapping an indexed column inside a function breaks the index. MySQL cannot use a B-tree index on the result of a function — it must compute the function for every row first.

-- Breaks the index on created_at:

WHERE YEAR(created_at) = 2024

-- Use a range instead:

WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

OR Conditions Across Columns

OR across different columns often forces full table scans because MySQL can only use one index per table per query in most cases. Restructuring as a UNION can allow each branch to use its own index.

The N+1 Query Problem

This is a common application-level mistake. An ORM fetches a list of 100 users, then for each user runs a separate query to fetch their orders — 101 queries instead of one. In Laravel Eloquent, the fix is eager loading:

-- N+1: triggers 101 queries

$users = User::all();

foreach ($users as $user) { $user->orders; }

-- Fixed: 2 queries total

$users = User::with('orders')->get();

In Django, use select_related() for foreign keys and prefetch_related() for many-to-many. In Sequelize, use the include option. The pattern is the same across all ORMs — specify what you need upfront.

Index Optimization: Beyond the Basics

Composite Indexes and Column Order

A composite index on (last_name, first_name) can satisfy queries filtering on last_name alone, or on both columns together — but not on first_name alone. The leftmost prefix rule determines this. Put the most selective column first, and structure the index to match the WHERE clauses your queries actually use.

Covering Indexes

A covering index includes all columns a query needs — so MySQL never touches the actual table rows. EXPLAIN will show Using index in the Extra column. This is one of the highest-impact optimizations available:

-- Query needs id, status, created_at

CREATE INDEX idx_covering ON orders (customer_id, status, created_at);

If the index covers everything the query selects, MySQL reads only the index structure, which is far smaller and faster than the full table.

Clustered vs Secondary Indexes

Over-indexing is a real problem. Each index adds overhead to INSERT, UPDATE, and DELETE operations because MySQL must keep all indexes consistent. Add indexes to solve real, measured problems — not speculatively.

Query Rewrite Techniques That Make a Real Difference

EXISTS vs IN for Subqueries

EXISTS short-circuits as soon as it finds a match. IN evaluates the full subquery and builds a result set. For large datasets, EXISTS is often significantly faster when checking for the presence of related rows.

-- Slower on large datasets:

WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'gold')

-- Often faster:

WHERE EXISTS (SELECT 1 FROM customers WHERE customers.id = orders.customer_id AND tier = 'gold')

Pagination at Scale

LIMIT 10 OFFSET 50000 is slow because MySQL scans and discards 50,000 rows. Keyset pagination avoids this entirely by filtering from the last seen row:

-- Slow OFFSET pagination:

SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 50000;

-- Fast keyset pagination:

SELECT * FROM orders WHERE id > 50000 ORDER BY id LIMIT 10;

MySQL Server Configuration That Affects Query Performance

Sometimes the query and its indexes are fine — the bottleneck is the server configuration. These are the variables that matter most in production:

The innodb_buffer_pool_size is the single most impactful configuration setting. If your working dataset fits in the buffer pool, disk I/O for reads drops dramatically. Check the buffer pool hit rate regularly — below 99% is a sign it is too small.

Optimizing for Tables With Millions of Rows

Table Partitioning

Partitioning splits a large table into smaller physical segments based on a column value — typically a date range. Queries filtering on the partition key only scan relevant partitions, skipping the rest entirely. This is called partition pruning and can reduce scan costs by orders of magnitude on time-series data.

Read Replicas and Caching

Read replicas offload SELECT queries from the primary server. Route reporting queries, analytics, and non-critical reads to replicas. Add a caching layer — Redis or Memcached — for queries whose results do not change frequently. Serving a cached result takes microseconds; even a fast SQL query takes milliseconds.

Archiving Old Data

Moving historical records to archive tables keeps active tables lean. A query on 2 million active orders behaves very differently than the same query on 200 million rows spanning a decade.

AI-Powered Query Optimization in 2026

A new category of tools analyzes query patterns, index usage, and execution plans at scale — and surfaces recommendations automatically.

• Percona Monitoring and Management (PMM) provides query analytics with automated index recommendations based on real workload patterns.

• OtterTune uses machine learning to tune MySQL configuration parameters — innodb_buffer_pool_size, sort_buffer_size, and others — based on observed workload behaviour.

• GitHub Copilot and similar assistants can now flag N+1 issues and missing index usage in ORM code during development.

These tools surface the right problems faster. But you still need to understand EXPLAIN output and indexing principles to evaluate their suggestions correctly.

Production Optimization Workflow

Do not optimize blindly. Follow this sequence every time:

1. Identify: Use the slow query log or performance_schema to find the worst offenders by total execution time, not just single-run latency.

2. Benchmark: Capture baseline execution time with SELECT BENCHMARK() or an external timing tool. You need a number to beat.

3. Analyze: Run EXPLAIN ANALYZE and read the output carefully. Look at type, key, rows, and Extra.

4. Index first: Most slow queries are index problems. Add or adjust indexes before touching the query itself.

5. Rewrite if needed: If indexing does not solve it, rewrite the query — remove SELECT *, fix OR conditions, restructure subqueries.

6. Tune configuration: If query and index are fine but performance is still poor, look at innodb_buffer_pool_size, tmp_table_size, and disk I/O.

7. Test in production conditions: Test with production data volumes, not a small local copy. Results differ significantly.

8. Monitor for regressions: Schema changes elsewhere can invalidate a previously optimal plan. Add query performance to your monitoring dashboards.

Common Bottlenecks and Their Fixes

Common Questions

When should I add an index vs rewrite the query?

Try the index first. Most slow queries are missing one. Rewriting is valuable when query logic is fundamentally wrong — like OFFSET pagination or OR across columns — but indexes solve the majority of cases faster and with fewer side effects.

How do I know if my index is being used?

Run EXPLAIN. Check the key column — it shows which index MySQL selected. NULL means no index was used. The type column also matters: ref, range, or eq_ref are good; ALL means a full table scan.

Does MySQL 8 change anything?

Yes. MySQL 8 added invisible indexes, descending indexes, and better optimizer handling of CTEs and window functions. EXPLAIN ANALYZE also became significantly more detailed and reliable in MySQL 8.

Final Thoughts

Slow query optimization is detective work. The slow query log and EXPLAIN ANALYZE tell you where to look. Indexing and query rewrites tell you how to fix it. Start with the worst offender, run EXPLAIN ANALYZE, fix one thing at a time, and measure. Applied consistently, that process handles the vast majority of MySQL performance problems in production.