Query profiling · Advanced · an hour with EXPLAIN open
How to optimize a MySQL database — Find the Query That Costs You, Then Prove the Index Worked
The site got slower as the data grew and you have no idea which query is responsible, so you are about to start adding indexes on instinct.
The short answer
Almost all database slowness comes from a handful of queries, so profile before you touch the schema. Query Monitor on WordPress, or the slow query log where you have access to it, names the statement and the caller; EXPLAIN then tells you whether the engine is scanning the whole table or hitting an index. Rows examined is the figure to write down, because it is what an index is supposed to reduce.
This runbook follows that order: find the offending statement, read its plan, add the narrowest index that fixes it, prove the improvement with the same EXPLAIN, and only then look at the cleanup and caching work that stops the problem returning. Optimising without a measurement is rearranging storage and hoping.
By the HostingFast team · Reviewed 24 August 2026
Advanced
Skill floor
an hour
Hands-on time
5
Numbered stages
24/7
Support cover
Written for someone comfortable in phpMyAdmin or a MySQL shell. Every destructive step here is preceded by an export, because a schema change on a live table is one of the few things on this site that is genuinely hard to undo in a hurry.
The two numbers that matter are rows examined for the query you are fixing, and total query time for the page that runs it. Capture both before you begin. If a change does not move either, it did not help, however sensible it looked.
Name the query before you touch the schema
Install Query Monitor and load the slow page while logged in. It lists every statement the page ran, ordered by duration, with the plugin or theme function that fired it. That attribution is usually the whole diagnosis: a single plugin issuing an unindexed meta lookup on every page view accounts for more slow WordPress sites than any tuning parameter.
Without WordPress, the slow query log does the same job. Set a threshold, leave it running through a realistic load, then read what accumulated. Either way you end with a specific statement rather than a feeling that the database is slow.
Read the plan, then index the narrowest thing that fixes it
Prefix the statement with EXPLAIN and read three columns: type, key and rows. type=ALL with a large rows figure is a full table scan. Add an index on the column the WHERE or JOIN filters on, re-run EXPLAIN, and confirm that key is now populated and rows has collapsed. That before-and-after pair is the proof the change worked.
Resist indexing everything. Every index is written on every insert and update and occupies disk, so a table carrying twelve speculative indexes is slower to write and no faster to read than one carrying the two the queries actually use. Export the table before adding an index to a live site.
The WordPress-specific number worth checking
Autoloaded options are fetched on every single request, so their total size is a fixed tax on every page. Sum the length of option_value where autoload is enabled and read the largest rows. Plugins removed months ago routinely leave bulky autoloaded settings behind, and past a few hundred kilobytes it is worth reading that list line by line.
The same applies to post revisions, expired transients and orphaned postmeta. These are not tuning parameters; they are volume that every query has to move past. Clearing them shrinks the working set, which is the cheapest performance win in the whole exercise and the one that needs no schema change at all.
The best optimisation is the query that never runs
Redis object caching keeps the results of repeated queries in memory, so the second identical lookup within a request never reaches MySQL. On a store or a membership site that removes a large share of total query volume, and it does it without touching a single index. Confirm it is actually connected rather than installed — the plugin reports its own status.
OPTIMIZE TABLE reclaims space after heavy deletion churn and is worth running occasionally, but treat it as housekeeping rather than tuning; it rebuilds the table and can lock it while it does. Our NVMe storage keeps that rebuild short, and the daily backup means you always have last night's copy to fall back on.
The hardware these queries were timed on
Every figure in this runbook was taken on the platform we operate: NVMe storage on every tier, LiteSpeed compiled into the web server, and phpMyAdmin a click away in cPanel. Disk latency is the floor under every query time, which is why the storage tier is not a detail.
Mailboxes on your own domain come as part of the plan — email is included rather than sold back to you at checkout.
- NVMe on every tier, so the disk is not the bottleneck
- phpMyAdmin in the panel for EXPLAIN without a shell
- Daily backups with restores you run yourself before a schema change
- SSH and shell access on the developer-focused plans
Why HostingFast
Standard on every plan
Attribution, not guesswork
You finish knowing which plugin fired the expensive query, because the profiler names the caller as well as the statement.
EXPLAIN before and after
Every index is justified by a rows-examined figure that fell, which is the only evidence that an index earned its write cost.
The autoload check
One query tells you what every page view is paying for options no plugin has needed since spring.
Export before you alter
Schema changes on live tables are the one genuinely awkward rollback here, so the export step is not optional.
Caching in its right place
Object caching removes query volume rather than hiding a bad plan, and the difference between those two is stated.
Scoped for an hour
An advanced job, but a bounded one: profile, plan, index, prove, clean up. Roughly an hour, most of it reading output.
Quick Start
From order to online
- 1
Profile the slow page
Query Monitor while logged in, or the slow query log with a threshold set. End with a specific statement and the function that called it, not an impression.
- 2
Run EXPLAIN and read three columns
type, key and rows. A full scan on a large table is your target; note the rows figure now so you have something to compare against later.
- 3
Export, then add one index
Take a dump of the table first. Index the column the query filters on, re-run EXPLAIN, and confirm key is used and rows has fallen. One change, one measurement.
- 4
Audit autoloaded options and debris
Sum the autoloaded option sizes and read the largest. Clear post revisions, expired transients and orphaned metadata; every query moves past less data afterwards.
- 5
Add object caching last
Redis keeps repeated lookups in memory so they never reach MySQL. Check the plugin reports a live connection, then re-time the page you started with.
Built In
Loaded onto every plan
- NVMe SSD storage on every tier, including the entry plan
- SSH, Git and Composer on the developer-focused plans
- Per-site PHP version switching from the control panel
- cPanel — the control panel most of the industry already runs
- Daily backups on every plan, with restores you run yourself from the panel
- Staging environments for rehearsing a change before it ships
- LiteSpeed caching compiled into the server, not bolted on by plugin
- In-place account upgrades, with no migration when you change plan
- Email addresses that run on your own domain name
- Human support on duty every hour of every day
Frequently Asked
What people ask us most often
Which tool shows me the queries a single page actually ran?
Query Monitor on WordPress: it lists every statement for the current request with its duration and the calling function, so a slow page becomes a named plugin in about ten seconds. Outside WordPress, enable the slow query log with a threshold and read what collects during a realistic load rather than during an idle afternoon.
Does OPTIMIZE TABLE lock the table while it runs?
It rebuilds the table, and on the storage engines most sites use that means the table is unavailable for writes while the rebuild happens. On a small table it is over in moments; on a large one, run it in a quiet window with a fresh backup in hand. NVMe storage keeps the rebuild considerably shorter than spinning disks did.
Will an object cache hide a badly planned query?
It will hide it from you and not from your visitors. Caching removes repeated executions but the first one still runs at full cost, and a cache miss under load puts the original problem straight back in the request path. Fix the plan with an index, then add caching to remove the volume.
Should every column in a WHERE clause get an index?
No. Indexes are written on every insert and update, so speculative ones make writes slower and reads no faster. Index the columns your profiled queries actually filter and join on, confirm with EXPLAIN that the index is being used, and drop any index you cannot point at a query for.
Keep reading
How to Lazy Load Images and Video
Find the LCP element, exempt it, then defer everything below the fold — beginner, roughly 15 minutes with a profiler open.
How to Understand Website Firewalls
What a web application firewall inspects, where it sits in the request path, and the rules worth touching — intermediate, roughly 20 minutes of reading.
Database (Glossary)
The storage layer behind a dynamic site, and the query cost that comes with it.
Email Hosting
Real mailboxes on your own domain, at one flat rate.
Secure Hosting
Imunify360, account isolation and hardened defaults for security-first builds.
Changing hosts? Run through our checklist first.
A straightforward sequence for a switch your visitors never feel: which files move first, how to shift email across without losing a single message, the right moment to repoint DNS, and the two mistakes behind almost all the downtime we get asked to rescue.
Give the queries faster disks.
NVMe on every tier, phpMyAdmin in the panel, SSH on developer plans, and a daily backup before every schema change.
View Email Hosting plans