Finding Slow Queries
Before you can fix what’s slow, you need to know which queries are slow and where they come from. A page that takes four seconds is rarely slow for one obvious reason. It’s usually a handful of queries, often triggered by a plugin or theme you’d never have suspected. Guessing wastes your time, so get the evidence first.
Scalability Pro fixes slow queries but does not log them. Finding them is the job of Super Speedy Performance Analysis, a free plugin that profiles your site and names the plugin or theme behind each slow query.
Profile your key pages
Performance Analysis profiles your front-end, WooCommerce and wp-admin pages and records, for each page, the generation time, SQL time, query count, rows returned, outbound HTTP time and peak memory. For every query it records which plugin or theme triggered it.
Where to look:
- Pages tab: click a page to see its slowest queries with their callers, and its outbound HTTP calls.
- Plugins tab: SQL time, query counts, rows fetched and the slowest query for each plugin across the whole analysis.
- Overview tab: the findings that matter most, each naming the component responsible and what to do about it.
To check one page, open it while logged in as an administrator and click Analyse this page in the admin toolbar. That works on front-end and wp-admin pages.
The caller matters because the same SQL can be fired from several places, and a query that looks like core WordPress is often triggered deep inside a plugin or theme hook. Knowing the caller tells you whether to add an index, change the query, or turn off the feature responsible.
Why a query is slow
Performance Analysis runs each slow query through MySQL’s EXPLAIN after profiling, so it reports the reason as well as the time: no usable index, a temporary table, or a filesort. It never executes the query again to do this. It also flags common slow query shapes, such as postmeta scans, SQL_CALC_FOUND_ROWS, ORDER BY RAND(), leading-wildcard LIKE searches and nested taxonomy joins, along with the same query repeated in a loop.
Click a slow query to copy its full SQL.
A query with no usable index is fast while its table is small and gets slower as the site grows. It’s the usual reason a site that was fine last year isn’t any more, and it’s the easiest kind to fix with an index.
Measure Scalability Pro’s effect
Scalability Pro’s Main tab installs Performance Analysis for you in one click. When you apply the good settings with it active, Scalability Pro profiles your key pages first, applies the settings, profiles the same pages again and shows which pages got faster, with the generation and SQL time for each.
Other tools
- Query Monitor shows every query on the single page you’re looking at, with its caller. It’s good for checking one page while you work on it.
- Your database server’s own slow query log records slow queries from every request, including cron and imports. Your host can usually turn it on for you.
Comments