Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11The reliable way to find a slow MySQL query is to measure the same problem in stages: capture a repeatable slow case, inspect its proposed plan with EXPLAIN, measure actual iterator work with EXPLAIN ANALYZE when your version supports it, make one evidence-based change, and run the workload again. The slow query log and Performance Schema show what has happened in real workloads; EXPLAIN predicts how one statement should run; EXPLAIN ANALYZE executes a statement and reports what actually happened.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
This workflow avoids guessing at indexes or rewriting queries blindly. It also separates an inefficient plan from a query that is waiting on locks, storage, CPU, or other concurrent work. The examples below use MySQL terminology and commands; adapt file paths, permissions, thresholds, and production change controls to your deployment.
As an Amazon Associate I earn from qualifying purchases.
What MySQL query profiling can tell you
“Slow” is a symptom, not a diagnosis. A statement may consume too many rows, choose a poor join order, scan a large table, spend most of its time in one iterator, or have a perfectly reasonable plan but wait for a lock or storage operation. Profiling gives each possibility a different kind of evidence.
| Method | What it observes | Evidence type | Granularity | Production considerations |
|---|---|---|---|---|
| Slow query log | Statements that exceeded the configured time and examined-row thresholds | Historical executions | Query statement | Usually the lowest-friction way to collect real slow cases; account for log volume and disk use |
| Performance Schema | Instrumented server and application activity, including statement summaries and waits | Historical measurements and aggregate workload data | Query, stage, and wait | Instrumentation choices affect overhead; use it to measure rather than guess |
EXPLAIN |
The optimizer’s proposed access and join plan | Estimated execution | Plan operation | Does not execute the statement |
EXPLAIN ANALYZE |
Actual iterator timing, rows, loops, and comparison with estimates | Measured execution | Iterator and plan operation | Executes the statement, so use a safe test or controlled production procedure |
Start with a repeatable use case where performance is demonstrably too slow. Without a stable request, parameter set, and workload context, a single timing can lead you toward a fix that helps one run and harms the normal workload.
#1 Best Overall
Step 1: Capture a repeatable slow case
Use the slow query log for a historical view
The slow query log records statements that take longer than long_query_time and meet the server’s examined-row threshold. It is useful when you do not yet know which statement is responsible, because it preserves evidence from normal application traffic rather than relying on a hand-picked example.
Check the current settings before changing them:
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'min_examined_row_limit';In an environment where you are allowed to change global settings, enable logging and choose a threshold appropriate to the workload:
Free tools Windows power users keep installed
One-click scans. No signup required.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;The one-second value is an example, not a universal target. A busy game service may need a much lower threshold to catch latency-sensitive requests, while a batch system may treat a longer statement as normal. Configuration persistence, log destination, permissions, and restart behavior depend on how your MySQL server is managed; verify those details before relying on a temporary setting.
Large logs are difficult to read line by line. The mysqldumpslow utility groups similar statements and can rank them by time, count, or other available sort modes. For example:
mysqldumpslow -s t -t 20 /path/to/slow.logUse the summarized output to select a query shape and then reproduce it with representative parameter values. Do not optimize a literal statement that is slow only because it contains an unusual one-off value unless that case matters to users.
Use Performance Schema when you need workload and wait data
Performance Schema is MySQL’s built-in instrumentation system for taking real measurements instead of “wild guesses.” It can summarize statement digests, count executions, show rows examined, and expose stage or wait activity. A digest groups structurally similar statements, allowing a high-frequency query to stand out even when individual executions are short.
A statement-summary query can rank workload shapes by total time and examined rows:
SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT,
SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;Use the result to ask two separate questions: which query consumes the most aggregate time, and which query is slow per execution? A frequently executed moderate query can be a larger capacity problem than a rare outlier. Performance Schema wait and stage instruments also help when the plan looks sensible but users still see latency: lock contention, I/O, CPU pressure, and concurrency can dominate execution time outside the optimizer’s row estimates.
Instrumentation is not a license to enable every possible event permanently. Select the statement, stage, and wait data needed for the investigation, observe overhead, and follow your production change process. Begin with the repeatable slow use case, then enable the measurements that can explain it.
Step 2: Inspect the proposed plan with EXPLAIN
MySQL describes EXPLAIN as providing information about how it executes statements. Run it against the exact query shape and representative predicates:
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →EXPLAIN SELECT ...;For a machine-readable plan, request JSON:
EXPLAIN FORMAT=JSON SELECT ...;Read the plan as a set of decisions rather than as a single “good” or “bad” label.
Access type and chosen index
Check how each table is accessed, which candidate indexes the optimizer considered, and which index it selected. A full table or full index scan is not automatically wrong: scanning a small table can be cheaper than navigating an index. On a large table, however, an unexpected scan is a prompt to examine predicates, data types, and available indexes before forcing a plan.
Estimated rows and filtering
The plan’s row counts are estimates. Compare them across steps and joins. A large estimated intermediate result can make a later operation expensive even if the final result contains only a few rows. The filtering estimate indicates how much of the candidate data is expected to survive a condition; weak selectivity can explain why an index does little work.
Join order, partitions, and extra operations
For multi-table statements, inspect join order and the access path used for every table. A poor order can create exploding intermediate rows. Check partition choices when partitioning is present, and read the Extra information for additional operations such as sorting, temporary processing, or other work that is not obvious from the access type alone.
Recommended Free Tools
JSON output is especially useful when nested plans, cost details, or join relationships are hard to interpret in the traditional tabular format. Save the plan before changing anything so you can compare the optimizer’s decision after each modification.
Step 3: Measure actual work with EXPLAIN ANALYZE
EXPLAIN ANALYZE was introduced in MySQL 8.0.18. It runs the statement and reports estimated cost and rows alongside measurements such as time to first row, iterator execution time, rows returned, and loops. Use it to test whether the optimizer’s assumptions match reality.
EXPLAIN ANALYZE SELECT ...;The most valuable comparison is estimate versus actual:
- Estimated rows far below actual rows: statistics may be stale or data may be skewed. Correlated predicates can also make independent estimates unreliable.
- Actual rows far below estimates: the optimizer may be carrying unnecessary work through later operators, even if the final result is small.
- High loop counts: a nested operation may be repeating an expensive lookup for every row from an earlier iterator.
- Most time in one iterator: tune that operator or its input first instead of rewriting the entire statement.
Because the statement executes, treat this command differently from ordinary EXPLAIN. A read query can still consume significant CPU, memory, and I/O. Document the parameter set, run it against a safe copy when possible, and schedule controlled production measurements when isolation is not possible.
Rank #4
Support and syntax vary by deployed MySQL version. The documented feature covers SELECT and, in releases that support them, multi-table UPDATE, DELETE, and TABLE statements. Confirm your server’s version and syntax before using it. Never casually run an analyzed write against production data: use a safe test, a controlled transaction strategy appropriate to the statement, or a production replica where that is operationally valid.
How to interpret the evidence
When estimates are wrong
If actual rows greatly exceed the estimate, first test refreshed statistics:
ANALYZE TABLE your_table;Re-run EXPLAIN and, where safe, EXPLAIN ANALYZE. If the mismatch remains, investigate skewed values, correlated columns, predicates that hide selectivity, and indexes that do not represent the way the query filters. Statistics refresh is a testable hypothesis, not a guarantee of improvement.
When a large table is scanned
Check whether the predicate can use an index without transformation. Functions or expressions applied to a column, implicit type conversions, and mismatched comparison types can prevent an efficient search. Verify that filter and join columns have suitable indexes and compatible data types. Do not force an index merely to eliminate a scan; compare measured work before and after.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →When a join explodes
Use both the estimated and actual row counts at each join boundary. Missing or weak join predicates, an index that supports only one side of the relationship, or a bad join order can multiply rows before later filters reduce them. Fix the source of the multiplication, then measure again.
When the plan looks reasonable but users still wait
Move beyond the optimizer plan. Performance Schema wait and stage data can reveal lock waits, storage latency, CPU saturation, or concurrency effects. A query can have an efficient access path and still be delayed by another session holding a needed resource. In that case, changing indexes may not address the real bottleneck.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Step 4: Apply the smallest evidence-based fix
Change one material factor at a time so the next measurement can be attributed. Typical candidates are:
- Indexes: add or adjust indexes for selective filter and join columns, considering the predicates’ order and the rows the query actually needs.
- Predicates: simplify costly conditions and remove expressions that make indexed columns difficult to search.
- Projection: return only required columns when reducing row and I/O volume matters.
- Statistics: refresh table statistics when estimates are demonstrably stale or inaccurate.
- Query shape: reduce unnecessary full scans or intermediate results only after the plan and measurements identify them as the cost center.
Keep the original query, plan, parameter set, schema change, and measured result together. An index that helps one statement can add write cost or change another statement’s plan. Review the broader workload before making a permanent schema change.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsStep 5: Re-run and validate the same workload
Repeat the exact capture and measurement sequence after each change:
- Run the same representative parameters and workload conditions.
- Capture a fresh slow-log or Performance Schema observation.
- Compare traditional and JSON
EXPLAINoutput with the saved baseline. - Use
EXPLAIN ANALYZEonly in the safe environment and version-supported cases where actual execution data is needed. - Check total response time, rows examined, rows returned, loops, and waits—not just one attractive number.
- Observe other important queries and write workload for regressions.
MySQL performance varies with data distribution, cache state, concurrency, hardware, and workload. A small benchmark result is evidence for that test, not a universal improvement percentage. Keep the change only when it improves the target case without creating an unacceptable cost elsewhere.
Production-safe profiling checklist
- Define the user-facing symptom and a repeatable request or parameter set.
- Choose historical capture (slow log or Performance Schema) before guessing at a query.
- Record the MySQL version, schema state, data scale, and relevant configuration.
- Use
EXPLAINfreely for read-only plan inspection; it does not execute the statement. - Treat
EXPLAIN ANALYZEas a live execution with resource and write risks. - Limit instrumentation and monitor its overhead.
- Change one evidence-backed factor, then rerun the same workload.
- Check lock, I/O, CPU, and concurrency effects when plan changes do not explain latency.
- Preserve rollback steps for index, statistics, and query changes.
Where optimizer trace fits
Optimizer trace can provide detail that complements EXPLAIN, especially when you need to understand why an access path or join order was considered. Its format and content are subject to change between MySQL versions, so treat it as diagnostic output tied to the deployed version rather than a stable interface for automation. Use it after the basic evidence—slow case, plan, actual work, and waits—has narrowed the question.
A practical decision path
Use the following order to avoid collecting detail that cannot answer the immediate question:
- Unknown culprit: start with the slow query log or Performance Schema digest summaries to identify query shapes from real traffic.
- Known statement, unknown plan: run traditional and JSON
EXPLAINand inspect access types, indexes, estimates, joins, partitions, and extra operations. - Estimate uncertainty: run
EXPLAIN ANALYZEin a safe, version-supported environment and compare actual rows, loops, and iterator time. - Plan is costly: test the smallest index, predicate, statistics, or query-shape change suggested by the evidence.
- Plan is reasonable but latency remains: inspect Performance Schema waits and stages, then investigate locks, I/O, CPU, and concurrency.
- Result is improved: validate against the same workload and monitor for regressions before declaring the change complete.
This sequence turns profiling into a controlled experiment: real workload identifies the target, the optimizer plan explains its intended work, actual execution reveals where time and rows are spent, and a repeat measurement decides whether the fix is real.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
