Overview
When a page is slow, the SQL text alone rarely explains why. EXPLAIN shows how the database plans to execute a statement. It helps identify scans, join order, index selection and temporary work.
Capture the real query
Use the SQL and parameter values produced by the application. A query tested with a tiny development table may behave very differently on production-sized data. Do not paste private customer data into public diagnostic services.
Run EXPLAIN
For a SELECT statement:
EXPLAIN
SELECT id, title
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20;
Review the access type, possible keys, chosen key, estimated rows and extra operations. A full scan is not always wrong for a small table or a query that reads most rows. A chosen index is not proof that the plan is efficient.
Investigate joins and sorting
Check whether join columns have compatible types and useful indexes. Watch for unexpectedly large intermediate result sets. Sorting or temporary tables can be acceptable for some workloads, but become important when they process many rows.
Compare actual behavior
Where supported, use an execution-plan feature that reports actual row counts and timing. MySQL and MariaDB differ by version and syntax, so consult the documentation for the installed server. Compare the estimated plan with observed behavior.
Improve one thing at a time
Test a candidate index, rewrite or filter change independently. Re-run the same query with the same parameters and compare both the plan and elapsed time. Avoid adding indexes simply because a column appears in a filter.
Checklist
- Reproduce the slow query with realistic parameters.
- Run EXPLAIN on the exact statement.
- Review row estimates, joins, access type and sorting.
- Verify data types and join predicates.
- Test changes against representative data.
- Confirm that improvements do not harm write performance.