The database is the bottleneck.
IDENTIFYING IT
A profiling tool shows time spent in database queries versus PHP execution.
If most of the time is queries, the database is the problem.
THE USUAL CAUSE
A missing index on a table that has grown.
A query that was fast with a thousand rows becomes slow with a hundred thousand, and slower still as it grows.
FINDING SLOW QUERIES
Enable the slow query log, or use a query monitoring plugin during a page load.
It names the query and the time taken.
ADDING AN INDEX
On the columns used in WHERE and ORDER BY clauses.
Back up before changing schema.
OTHER CAUSES
Tables bloated with expired transients, old revisions, log entries and spam Queries loading enormous result sets An application running many queries per page
CLEANING UP
Remove expired transients, limit post revisions, clear old logs, delete spam.
These accumulate quietly and affect every query.
OPTIMISING TABLES
After large deletions, optimise the affected tables to reclaim space.
