Your Query Takes Four Seconds: How to Find It and Fix It in MySQL 8.4
The page takes six seconds and the whole problem fits in one line of SQL. Before touching anything, measure: this is the path from the slow query log to EXPLAIN, from there to the missing index, and back to measuring to prove the fix is real.
Step 1: Find the Guilty Query Without Restarting the Server
The slow query log can be turned on at runtime with system variables, no MySQL restart needed. Three variables and one decision about where to write are all you need to control.
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_output = 'TABLE';With log_output='TABLE' entries land in the mysql.slow_log table, which you query with SQL and filter like any other table; with 'FILE' they go to a file on the server. The table is handier when you have no access to the database host's filesystem. long_query_time accepts decimal values, so starting at half a second is usually a sensible threshold.
What to Read in Each Entry
Four columns matter in every record: query_time (how long it took), lock_time (how long it waited on locks), rows_examined (rows the engine had to read) and rows_sent (rows returned). The comparison between the last two is the most useful signal and the one almost nobody checks: a query that examines four million rows to return twenty is doing enormous work for nothing.
Read also
SELECT query_time, rows_examined, rows_sent, LEFT(sql_text, 120) AS sql
FROM mysql.slow_log
ORDER BY rows_examined DESC
LIMIT 10;log_queries_not_using_indexes: Useful, With Caveats
This variable logs every query that does not use an index, even a very fast one. On a hundred-row table that is normal and harmless, so enabling it blindly fills the log with noise and hides what actually hurts. If you use it, pair it with min_examined_row_limit so only queries that also read many rows make the cut.
The Fast Alternative: the sys Schema
MySQL ships the sys schema with views that are already aggregated: statements doing full table scans, and indexes going unused. They are the quickest way to prioritize before reading a single plan: see which query consumes the most total time, then investigate that one.
Step 2: Read the Plan With EXPLAIN Without Getting Lost
EXPLAIN works on SELECT, DELETE, INSERT, REPLACE and UPDATE and answers one question: which path did the optimizer choose? Five columns cover almost everything.
type: how it accesses the table.ALLis a full scan;range,refandconstare better, in that order.key: the index actually used (different frompossible_keys, which only lists candidates).rows: rows the optimizer estimates it will read. An estimate, not a fact.filtered: estimated percentage of rows surviving the filter.Extra: the warnings that matter show up here.
Red flags read like this: type: ALL with a high rows value means a full scan of a large table; Using filesort means the engine sorted the result on its own, without help from an index; Using temporary means an intermediate temporary table, typical of certain GROUP BY and DISTINCT operations; and Using index is the good news: the index contains everything the query needs, so there is no need to touch the table.
FORMAT=TREE and FORMAT=JSON
The classic format is a table, useful for a query with one or two tables. With several joins or subqueries, EXPLAIN FORMAT=TREE shows the plan as a tree, and FORMAT=JSON adds details the classic format hides, such as per-block costs. Start with the classic one and switch only when the query gets complicated.
Step 3: Measure What Actually Happened With EXPLAIN ANALYZE
EXPLAIN ANALYZE has been available since MySQL 8.0.18 and does something different: it runs the query and shows, per iterator, the actual time and the actual rows. Where classic EXPLAIN says "I think I will read 12 rows", this one says how many it read and how long each step took.
A warning you cannot skip: because it executes the query, an EXPLAIN ANALYZE on a heavy UPDATE or DELETE really does the work. In production, wrap it in a transaction you roll back or reserve it for read-only queries.
Iterators Are Read in Order
The output is read top to bottom and inside out: nested nodes are the steps feeding the parent. The actual time fields (first and last record of the iterator), rows and loops show where the time goes: a loop running a thousand times with a low per-iteration time can weigh more than a single large scan.
When the Optimizer Lies
Comparing the plan's estimated rows with the actual ones is the most honest diagnostic there is. If the estimate says 10 and the real plan says 400,000, the problem may not be a missing index but outdated statistics. ANALYZE TABLE recalculates key distribution, and histograms (created with ANALYZE TABLE ... UPDATE HISTOGRAM) help when data is heavily skewed and the optimizer keeps missing. On large tables this operation has a cost: pick your time window.
Step 4: Indexes, and the Four Reasons They Are Not Used
An index is not free: it speeds up the read pattern it was built for and makes every write more expensive, plus it takes up space. That is why you add one after measuring, not before.
Composite Index: Equality First, Range Later
On an index over (a, b, c) the leftmost prefix rule applies: the optimizer can use it to filter by a, by a, b or by a, b, c, but not by b alone. Column order defines which queries the index serves, which is why equality columns usually come first and range or sort columns go last.
Trap 1: Wrapping the Column in a Function (or a CAST)
A query like WHERE DATE(created_at) = '2026-09-01' applies a function to the column and renders the created_at index useless. Write the condition as a range instead: WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'. The same happens when you CAST the column you are comparing.
Trap 2: LIKE '%text%' Cannot Use an Index
A leading wildcard prevents using the index order, because there is no way to walk it from a known point. For full-text search the tool is FULLTEXT, not a B-tree index in disguise. A LIKE 'text%' can use the index: there the prefix is defined.
Trap 3: Mismatched Types and Collations Force Conversions
Comparing a VARCHAR column with a number, or columns with different collations in a join, forces the engine to convert values and can take the index out of play. Reviewing declared types is boring and is often the real cause of an unexpected type: ALL.
Trap 4: ORDER BY and GROUP BY That Do Not Follow the Index
When the requested order matches the index order and the filter conditions allow following it, MySQL skips sorting. When it does not match, Using filesort appears and the engine sorts in memory or on disk. If you see that flag on a frequent query, the fix may be an index whose sort columns come last in the composite index.
Covering Index and Invisible Indexes
An index containing every column the query reads and filters lets it answer without touching the table (the Using index flag from earlier). In practice that means avoiding SELECT * and selecting only what you need. And to test the effect of removing an index without dropping it, invisible indexes exist: ALTER TABLE ... ALTER INDEX ... INVISIBLE keeps it declared while the optimizer ignores it, so you can measure in production before deciding on a DROP INDEX.
Patterns That Eat Performance (and How to Fix Them)
Deep Pagination: Why OFFSET 100000 Is Slow
LIMIT 20 OFFSET 100000 does not jump to row 100,000: it reads and discards everything before it. The alternative is keyset pagination, filtering by the last value seen on an indexed column: WHERE id > :last ORDER BY id LIMIT 20. It is faster and it does not shift when new content arrives.
SELECT *, Repeated Subqueries and N+1
Selecting every column blocks the covering index and returns more data than you use. Subqueries and aggregates repeated within the same request are usually solved with one aggregate query and a JOIN. And the classic ORM N+1 problem is fixed in code (loading the relation in one go), not with an index: they are different problems and it pays not to mix them up.
From the Engine to Laravel: Get the Real SQL and Measure It
If the slow query comes from Eloquent, the first step is to see it as-is, with bound values already inlined, so you can paste it into the MySQL client and run EXPLAIN on it.
DB::listen(function ($query) {
logger()->debug($query->toRawSql(), ['ms' => $query->time]);
});toRawSql() returns the SQL with values inlined, which is exactly what you need to reproduce it. And in migrations, when declaring a composite index, column order is a performance decision, not a style detail: write it with the queries you will run in mind. Remember that an index does not fix redundant queries: if the application asks for the same thing three times in one request, the problem is in the logic.
How to Prove It Improved (and That You Did Not Fool Yourself)
The honest way to verify an optimization is not the stopwatch, it is rows_examined before and after. A time that drops without rows examined dropping may just be a warm cache. And be careful where you measure: on your machine the dataset is small, the buffer pool is warm and everything looks fast; in production the plan can differ because volume, value distribution and statistics all change. Change one thing at a time, write down the before and after, and let real traffic flow before signing off on a tuning change.
Conclusion
The order matters more than the tools: measure, read the plan, measure again with real data, and only then touch indexes. If you keep one idea, keep the rows-examined comparison, because it is what separates a real improvement from a feeling. On this blog you also have the guide to eliminating the N+1 problem in Laravel, advanced query builder with subqueries and SQL expressions and the Octane guide to speed up your application: three pieces that combine well with this procedure.
