Slow query log analysis: where the site loses speed
Some pages respond noticeably longer than others: the catalog takes seconds to open, a product page opens instantly, and under load the difference turns into dropped connections.
Externally it looks like a lack of capacity, although CPU and memory are half-loaded — the bottleneck of the site's database hides in one or two templates.
Slow query log analysis helps to figure it out. The database server writes to a separate file every query whose execution exceeded a specified time threshold; the threshold and collection method are set in the configuration.
The output is not guesses but a list of queries with call frequency and total time over the period: it shows exactly what slows down the catalog under load.
The picture repeats from project to project: there are few heavy queries — selecting products with prices and stock, calculating filters, counting items for pagination, assembling trade offer properties. Each runs on every page view, and on a catalog with filters — several times per user request.
The causes are usually structural: there is no composite index for the filter conditions, all columns are selected instead of the needed ones, sorting is done by an unindexed field, identical queries are repeated without an intermediate cache.
As the catalog grows, the time of such queries grows faster than the number of products, so the problem manifests as a jump — after an import or seasonal traffic growth.
Slow database queries are not treated by adding resources: while a heavy query hangs on every page view, capacity only pushes the limit further away.
Next we analyze how the log turns into changes — from reading the execution plan to verifying the result with a repeat measurement.
What heavy query analysis gives the site
The analysis starts with the slow query log: we look at which database calls exceed the threshold, how often they repeat, and what they read — an index or the entire table.
This data shows exactly where time is lost: most often in catalog queries with filters, counting the number of products, and recalculating discounts in the cart.
The first measurable result is that catalog response time stops depending on filter complexity. When a heavy query goes to a composite index instead of a full scan, the category page is served in stable time both for thousands of items and tens of thousands.
Next is cart and checkout performance. Extra database calls at each step are removed or combined into one query, so adding a product and proceeding to payment do not hit response waiting, and the share of slow pages in total traffic drops.
Separately, we look at database load: query queues stop growing at peak traffic because part of the work is removed by indexes and query caching.
Along with this, a whole class of failures due to locks goes away — checkout transactions no longer wait for row release, and orders are not lost during sales hours.
This is the checklist by which we accept the work: before and after, we compare the number of slow queries per unit time, catalog response time, the share of pages served longer than the threshold, and the number of lock-related failures. E-commerce site acceleration here is not a general feeling but the difference in these numbers.
Methods for collecting and analyzing query statistics
Query statistics are collected in three ways, and the choice between them is based on two criteria: how long the site can tolerate slowdown and whether there is an up-to-date database copy for experiments.
Enabling slow query logging in MySQL is the cheapest path, but it has a cost: the log file grows quickly, and at peak load, writing adds overhead to processing each query.
Analyzing the log on a site copy removes risk for the production environment: there we also test indexes and view the execution plan before changing anything on the live server.
Polling active connections gives a "here and now" snapshot but does not show what happened during a nightly peak or a short traffic spike.
| Option | When it fits | Limitations |
|---|---|---|
| Slow query log on the production server | Need a snapshot under real load without a separate staging environment | File size grows, there is write overhead, threshold is set blindly |
| Analysis on a site copy | There is a fresh database dump and time for experiments | The copy lags in data volume, some anomalies are not reproduced |
| Polling active connections | Diagnosing a prolonged incident right now | Only current queries are visible, short spikes remain unnoticed |
We combine, not choose
We start with a short logging window on the production server to capture real patterns, then move the log to a copy and analyze it there.
Comparing approaches to database diagnostics gives an answer as to which queries to change first and what increase in page response time this will produce.
Workflow: from log collection to result verification
The MySQL slow query log captures a symptom, not the cause: it includes individual queries longer than a specified threshold, but a single line does not show whether there is no index, statistics are outdated, or a heavy query is called from a template on every page.
We structure the database optimization stages so that each step narrows the list and ends with a measurable result — this is the work plan for speeding up the site.
- Collect the log under real load — a working day plus a peak interval. Set the threshold so that background noise is cut off and rare but heavy queries are not lost.
- Group by patterns: queries identical in structure are combined into one item, and we calculate each pattern's contribution to total time.
- Analyze execution plans: where the table is read in full, where sorting goes through a temporary table, where the join order is chosen poorly.
- Prepare changes: composite indexes for conditions and sort order, rewriting the query in the template, caching repeated queries. We cut off extra indexes — they slow down writes.
- Test on a copy with the same data volume: compare time and plan before and after, make sure the query returns the same rows.
- Roll out in portions with a rollback point: first indexes, then template changes — if there is a regression, rolling back one step does not drag the others with it.
Verification after query optimization
After rollout, we take the same metrics as at the beginning: response time of key pages, number of queries per page, and share of queries above the threshold.
If a section did not speed up, we return to its execution plans — usually the condition was rewritten but the index for it has not been created yet.
Regulations, schemas, and settings: what remains with you
The work ends not with speeding up one page but with a set of materials that your team can use to continue without us. Below is what exactly is handed over after the stage of slow query log analysis and changes.
- Report on database bottlenecks: heavy queries with the execution plan, an explanation of why it is that way, and an assessment of their contribution to overall page response.
- Schema and index changes: which fields, composite and partial indexes were added, why exactly this set was chosen, and which queries were abandoned as ineffective.
- Log settings: duration thresholds, file rotation, parsing formats — so that new slow queries are captured and the disk does not grow indefinitely.
- Check queries for self-verification: running them is enough to see whether degradation has returned, without reading the entire log.
- Database maintenance regulations: check frequency, threshold values, and the procedure for handling load growth after an update.
- Rollback procedure: which changes are reversible, where structure snapshots are stored, and how to restore previous behavior in one step.
- Documentation after the work: a map of table relationships, a list of "heavy" code areas, and what should be rewritten in the next iterations.
Such a package turns one-time optimization into a manageable process: you see where the database slows down and can verify it yourself.
Case study: catalog under load before and after optimization
The e-commerce site acceleration case began with the fact that a slow query log was kept, but no one looked at it. The catalog has about 40,000 items, with a filter by several dozen properties.
Before optimization, the median catalog page response was around 2.5 seconds, and every fifth page did not fit within three seconds — the customer left without waiting for the results.
Log analysis revealed three sources.
Item selection went across the entire table with sorting by an unindexed field; filter properties were joined without composite indexes, so one combination of conditions generated dozens of calls; trade offers were fetched with a separate query for each product card instead of one batch read.
Metrics before and after optimization
Optimization of catalog queries came down to specific changes: composite indexes were assembled for real filter combinations, the set of readable fields was reduced, extra joins were removed, and trade offers are fetched in batches.
Pagination was switched to key-range reading instead of offset, and heavy queries were cached with invalidation on product change.
After the changes, the median catalog response is around 0.4 seconds, the share of slow pages dropped from 21% to 3%, and response time under peak stopped growing with the number of visitors.
The log was left enabled, and an alert threshold was attached to it: a new slow query arrives as a signal to engineers, not as a customer complaint. The project is on 1C-Bitrix, so the same techniques are repeatable on any catalog of this platform.
How does query analysis differ from site code optimization?
Query analysis and code changes are not competing approaches but two steps of the same diagnostics. First, you need to understand where time is lost: in the database or in the logic of the application that makes hundreds of calls to it.
Until the bottleneck is localized, optimization proceeds at random and the result cannot be predicted.
The cause can usually be distinguished by symptoms. If a specific scenario is slow — catalog with filters, sorting, pagination — while the rest of the site responds normally, the issue is more often in queries: heavy queries without indexes, aggregates over large tables.
When the entire site responds slowly or the same blocks are slow on different pages, the root is in templates and logic: repeated queries of the same data set, incorrectly configured component caching.
Query analysis provides facts: execution time, number of rows examined, share of queries without an index, repeated calls. This is enough to distinguish one heavy query from hundreds of fast ones that together consume the page response.
Measurements are taken on production traffic — slow query log plus a profile of the problematic scenario: a staging environment with an empty database does not reproduce such a picture.
If the root is in the database, changing code does not remove the problem, only masks it. And vice versa: when each query runs instantly but there are hundreds per page, indexes will not save you — logic and caching need to be reworked.
Therefore, the choice between query analysis and code changes is decided by measurements, not habit.
The workflow is as follows: take measurements for the problematic scenario, separate time between the database and the application, confirm the hypothesis with a targeted change, and only then plan the changes. The output is a list of changes with the expected effect for each item.
Technical doubts before starting work
Before starting, almost always the same questions are asked: will visitors suffer from enabled diagnostics, will the site need to be stopped, and what to do if heavy queries come not from our code. We answer directly — these answers determine how we structure work on 1C-Bitrix.
Will statistics collection slow down the site for visitors?
No. The slow query log is maintained by the database server itself, not the application: the overhead is writing a row to a file, fractions of a percent of response time.
We set a threshold below which a query does not enter the log — only truly heavy queries remain under observation. This logging is enabled and disabled on the fly, without restarting the server or stopping the site.
Will the site need to be stopped during analysis?
No. Primary diagnostics are passive: we read the already accumulated log and query execution plans, changing nothing in the running system.
Changes are made one at a time — the modified query, index, or setting is first tested on a data copy, then on a small share of traffic, and rolled back with one command. No maintenance window is required.
Heavy queries come from third-party modules and updates — will you speed them up?
Yes, and usually this is the most noticeable part of the log. Third-party modules, old components, and accumulated updates bring queries without indexes and with excessive joins.
We do not rewrite third-party code: we add indexes, caching, and settings at the database and configuration level, and record contentious areas in the report so that the next update does not wipe them out.
Let's discuss the task: where to start and what is needed to begin
To make the optimization discussion concrete, a minimum of input data is needed — without it, any estimate turns into guesswork. It can be collected remotely: we take some database state snapshots ourselves if remote access is available.
What helps to identify the causes of slowdown faster:
- Access: hosting panel or server over a secure channel, an administrator account with rights to read settings and logs.
- Slow query log for an interval with peak load. If logging is not enabled — we will advise how to enable it for a few days without affecting site operation.
- Database copy and server parameters: table structure, volumes, cache and connection values.
- List of problem areas: page addresses, response time, which actions are slow — catalog, search, checkout, 1C exchange.
- Load expectations: peak number of concurrent visitors and planned spikes from advertising and newsletters.
- Regulations: who on your side approves changes and during which hours measurements are allowed.
If something from the list is missing, it does not block the start. Describe the task in free form: we will ask clarifying questions and come back with an estimate, work plan, and metrics by which we will measure the result before and after the changes.