Most slow MySQL queries are fixed by one of three changes: adding a missing index, rewriting a query that forces a full scan, or correcting a schema or configuration choice that makes every read expensive. Work in that order, cheap fix first, and measure each change with EXPLAIN and the slow query log before moving on. The steps below start where MySQL actually records the problem and end with the configuration levers you should touch last, because most of the gain hides in the first two.
Slow queries are rarely a mystery once you can see what the optimizer decided. The slow_query_log in MySQL 8.0 records every statement above long_query_time, which defaults to 10 seconds, and EXPLAIN shows whether a query uses an index or scans the whole table. Those two tools turn guesswork into a checklist. The process works the same on MySQL 8.0 and 8.4, though 8.4 changed some defaults worth knowing before you tune anything.
Turn On the Slow Query Log and Read What It Captures
The first move is making the problem visible. MySQL ships with the slow query log off by default, so slow statements go unrecorded until you enable it. On a live server you can flip it at runtime without a restart: SET GLOBAL slow_query_log = 'ON'; and SET GLOBAL long_query_time = 2; capture anything running longer than two seconds. Persist those in my.cnf under [mysqld] so a reboot doesn't silently switch logging back off.
The log line itself is dense but useful. Each entry carries the query time, the Lock_time, rows examined, and rows sent, and the gap between examined and sent is the tell. A query that examines 200,000 rows to return 50 is scanning; one that examines 60 and returns 60 is fine and slow only because someone ran it 4,000 times a minute. Read those two numbers before touching anything else.
Enable log_queries_not_using_indexes with care. It flags every query with no index, including trivial lookups on tiny tables, and on a busy server it can flood the log until the disk fills. Set it temporarily during an investigation, review the output, then turn it back off; leaving it on in production is a common way to discover the log is 40 GB.
Sort the captured statements by total time, not average. A 90 ms query firing 30 times a second costs more wall-clock time than a 3-second report run once an hour, and fixing the frequent small one usually wins. The slow log gives you the raw material; the next step is understanding why each offender is slow.
Run EXPLAIN and Spot a Full Table Scan in Seconds
EXPLAIN is the single most useful command in query tuning, and its output answers one question: does this query read an index or the whole table? Put EXPLAIN in front of any slow statement and look at the type column. A value of ALL means a full table scan; index means a full index scan, often nearly as bad; range, ref, and eq_ref mean the engine is narrowing rows before reading them. Anything at ALL on a large table is your first target.
Two other columns matter. rows estimates how many rows MySQL expects to examine, and a number in the hundreds of thousands on a table with 5,000 rows means the statistics are stale, not that the query is hopeless. Extra reveals the real cost: Using filesort means MySQL sorts results after reading them, and Using temporary means it builds a temporary table, both of which balloon as row counts grow.
On MySQL 8.0, EXPLAIN ANALYZE goes further and actually runs the query, reporting real timings against the estimates. That contrast is gold: if the optimizer estimated 100 rows but the query examined 50,000, the statistics are wrong and ANALYZE TABLE may fix more than any index would. Run it on a replica or in staging first, since it executes the statement for real.
A frequent trap is trusting EXPLAIN estimates alone. The optimizer can pick a bad plan because it thinks a column is more selective than it is, and the fix is refreshing statistics with ANALYZE TABLE table_name; rather than adding an index the planner won't use anyway. Read the plan, then verify with real timing.
Add the Missing Index the Plan Is Begging For
If EXPLAIN shows ALL on a table with real row counts and the query filters on a column, the fix is usually an index on that column. Consider a query like SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC;. Without indexes it scans every order, then sorts. A composite index on (customer_id, status, created_at) lets MySQL jump straight to the matching rows and read them already in order, killing both the scan and the filesort.
Column order in a composite index matters more than most people expect. MySQL uses the leftmost columns first, so (customer_id, status, created_at) serves queries filtering on customer_id alone, on customer_id plus status, and on all three, but not on status alone. Put the equality-filtered columns before the range or sort column, and put the column used in ORDER BY last so the index order matches the requested output.
Watch the index count. Every index you add speeds reads but slows INSERT, UPDATE, and DELETE, because MySQL maintains each one on write. A table carrying 12 indexes on a write-heavy workload is a common cause of its own slowness, and dropping three unused indexes can be as valuable as adding one. Check usage with SHOW INDEX FROM table_name; and, in MySQL 8.0, the sys.schema_unused_indexes view, which quietly lists indexes the optimizer never touched since the last server restart.
Add indexes one at a time and re-measure. A composite index that seems logical on paper can be ignored if a cheaper existing index already covers the query, and stacking indexes without measuring just adds write overhead. Change one thing, run EXPLAIN again, confirm type improved.
Stop Leading Wildcards and Functions From Killing Indexes
Many slow queries already have an index and ignore it, which frustrates people who did the right thing. The classic culprit is a leading wildcard: WHERE name LIKE '%smith' cannot use an index on name, because the index is sorted from the first character and the engine has no starting point. LIKE 'smith%' uses the index fine. Rewriting the pattern or adding a full-text index on the column is the fix.
Wrapping an indexed column in a function does the same damage. WHERE DATE(created_at) = '2024-05-01' forces MySQL to compute DATE() on every row and can't use the index on created_at; write WHERE created_at >= '2024-05-01' AND created_at < '2024-05-02' instead and the index applies. The same trap hits WHERE YEAR(order_date) = 2024, WHERE UPPER(email) = ..., and arithmetic on indexed columns. Keep the column bare on the left side of the comparison.
Implicit type conversions hide here too. Comparing a VARCHAR column to a number, as in WHERE phone = 5551234, makes MySQL convert the column to a number for every row and skip the index. Store phone numbers and zip codes as strings and compare them as strings. The EXPLAIN plan often shows this as a scan when you swear an index exists.
If a query genuinely needs pattern matching in the middle of a string, a full-text index (FULLTEXT) on InnoDB handles word searches far better than LIKE '%...%', and for fuzzy needs a dedicated search engine may be the honest answer. Don't force a B-tree index to do a job it can't.
Fix the Schema and Data Type Choices Underneath
Some slowness is baked into the table design and no query rewrite will save it. A TEXT or BLOB column in the middle of the row forces MySQL to store overflow data elsewhere and can slow every read; moving large columns to a side table often helps. Storing dates as strings, or using utf8mb4 where latin1 would do, adds bytes to every row and every index, and the cost compounds at millions of rows.
Primary key choice matters on InnoDB. A random UUID as the primary key scatters inserts across the B-tree and causes page splits; a monotonic AUTO_INCREMENT integer key keeps inserts sequential. If you need UUIDs for external references, keep an auto-increment primary key and index the UUID as a secondary column. This single change can cut insert times dramatically on write-heavy tables.
Normalize where it prevents redundant wide rows, denormalize where a join is the bottleneck, but decide with data. A JOIN between a 10-row lookup table and a million-row fact table is cheap; a JOIN between two large tables on an unindexed column is a disaster. Check that join columns are the same type and indexed on both sides, because a type mismatch between joined columns forces a conversion that defeats the index.
Column width affects memory too. InnoDB's buffer pool caches rows and index pages, so a table with 20 wide VARCHAR(255) columns that mostly hold short strings wastes cache and pushes hot data out. Right-size the columns to what they actually store, and the same buffer pool holds more of the working set.
Read Configuration Defaults That Changed in MySQL 8.4
Configuration tuning is the last lever, not the first, but a handful of defaults deserve checking. innodb_buffer_pool_size defaults to 128 MB, which is tiny for any server with more than a few gigabytes of RAM; the standard guidance is to size it to 50β75% of available memory on a dedicated database host. A buffer pool smaller than the working set means every query hits disk, and no index fixes that.
MySQL 8.4 changed some defaults that surprise people upgrading from 8.0. One is innodb_change_buffering, which defaults to none in 8.4 versus all in 8.0; workloads with heavy secondary index writes may see different behavior after the upgrade. Another is that mysql_native_password is disabled by default in 8.4, so older client libraries can fail to connect until the plugin is re-enabled or the client is updated. Confirm the current default on MySQL's official documentation for your exact version before assuming.
innodb_flush_log_at_trx_commit trades durability for speed. At the default of 1, every commit writes to disk, which is safe but slow; setting it to 2 writes to the OS cache and flushes once a second, a common compromise for read-heavy workloads that can tolerate losing a second of writes on a crash. Don't change it to 0 on a system where a power loss must not lose committed transactions.
tmp_table_size and max_heap_table_size control how large an in-memory temporary table can grow before MySQL spills it to disk. Setting them equal and raising them together (to, say, the same value) keeps GROUP BY and DISTINCT operations in memory longer, but set them too high and a large query can exhaust RAM. Change one configuration value at a time and watch the slow log afterward.
Measure the Fix and Recognize the Queries Tuning Cannot Save
Always confirm the change worked with a before-and-after EXPLAIN and a timed run of the actual query. A composite index that moved type from ALL to ref and cut a 2.4-second query to 90 ms is a real win; an index that changed nothing on the plan is costing write overhead for no read benefit. Compare rows examined against rows returned to confirm the engine is now narrowing instead of scanning.
Prove performance on a dataset that matches production. A query that looked fast on 10,000 test rows can collapse at 10 million, because the optimizer's plan choices depend on row counts and index selectivity. Clone a slice of production data into staging, run the workload, and check the slow log again after tuning. Numbers from a toy table mislead.
Some queries resist every index and rewrite, and that's where you stop tuning SQL. Reports that join five tables and aggregate millions of rows, or dashboards that count huge ranges on every page load, belong in a summary table refreshed on a schedule or a separate analytics store. Precomputing nightly into a smaller reporting table can turn a 40-second dashboard query into a 200 ms lookup. Caching the result for a few minutes also makes sense when the underlying data changes slowly.
Know when the bottleneck isn't the query at all. If the server is CPU-bound, out of buffer pool, or waiting on disk, the same query will stay slow no matter how elegantly you write it. Watch for Lock_time in the slow log: high lock time means contention, not a bad plan, and the answer is transaction design and isolation level, not an index.
FAQ
What is the default slow query time threshold in MySQL?
long_query_time defaults to 10 seconds in MySQL 8.0 and 8.4, so a query under that runs unlogged unless you lower it. Most production tuning starts by dropping it to 1 or 2 seconds to surface the frequent-but-not-huge offenders that dominate total time.
Does adding more indexes always make queries faster?
No. Each index speeds some reads but adds maintenance cost to every insert, update, and delete, and the optimizer may ignore an index that doesn't match the query shape. Index the columns the slow log actually implicates, then verify with EXPLAIN that the plan uses them.
Why does my query ignore an index that exists on the column?
Leading wildcards (LIKE '%x'), functions around the column (DATE(col), UPPER(col)), and type mismatches between the column and the compared value all prevent index use. Rewrite the predicate so the bare column sits alone on the left of the comparison, and the index applies again.
Is EXPLAIN ANALYZE safe to run on production?
It executes the statement for real, so a slow or heavy query will run in full. Run it on a read replica or in staging, especially on write statements, and prefer plain EXPLAIN when you only need the estimated plan.
Can a schema change fix slowness that index tuning cannot?
Yes. A random UUID primary key, oversized TEXT columns, and mismatched data types between joined columns impose costs no index removes. Fixing the primary key to an auto-increment integer or moving large columns to a side table can help in cases where query rewriting stalls.
How much RAM should the InnoDB buffer pool get?
On a dedicated database host, common guidance is 50β75% of available memory, starting well above the 128 MB default. A buffer pool smaller than the working set forces disk reads that make even indexed queries slow; size it to the data your hot queries touch.
When should I move a slow query out of MySQL entirely?
When it aggregates or joins millions of rows on every request and no index helps. Precomputing results into a summary table or caching the output for a few minutes usually beats any SQL-level fix for dashboards and large reports.