MySQL Query Profiling for Bitrix: Audit & Optimization

A catalog page with 30,000 products generates 150 SQL queries, and the MySQL server hits 90% CPU — this is not a code problem, it's a lack of profiling. Without slow_query_log, you guess which query consumes resources — profiling gives a clear answer. Over a decade we have conducted more than 50 MySQL audits on 1C-Bitrix projects: every second project had similar patterns — N+1 queries, missing indexes on key tables (b_iblock_element, b_catalog_price), suboptimal InnoDB buffers. The result — pages load in 5–7 seconds, the server crashes under peak load. For diagnostics we use slow query log, pt-query-digest and EXPLAIN ANALYZE, as well as the built-in Bitrix tracker for ORM queries. Average page load time saving after optimization — 50–70%. Profiling MySQL queries in Bitrix reduces CPU load by 3-5 times, and TTFB by 60-80%. Typical savings on server infrastructure — significant depending on scale.

Step-by-step guide: enabling slow_query_log

1. On the MySQL server execute SET GLOBAL slow_query_log = 'ON';
2. Set the threshold: SET GLOBAL long_query_time = 0.5;
3. Specify the log file: SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
4. Enable logging queries without indexes: SET GLOBAL log_queries_not_using_indexes = 1;
5. For permanent configuration, write parameters in /etc/mysql/conf.d/slow.cnf.

How MySQL query profiling is performed in 1C-Bitrix?

The foundation is the MySQL slow query log. We enable it for 1–2 days with a threshold of 0.5 seconds. For production we use a configuration file with the parameter min_examined_row_limit = 100 to filter out fast queries by primary key.

Analysis via pt-query-digest

Percona Toolkit is the industry standard. The utility groups queries by pattern, shows frequency and total time.

pt-query-digest /var/log/mysql/slow.log --limit 20 --report-format query_report > /tmp/slow_report.txt 

On Bitrix projects, the top 5 problematic queries usually include:

• N+1 when fetching properties (b_iblock_element_prop_s*)
• Per-record price fetching (b_catalog_price)
• COUNT without index
• Full-text search on a large table
• Contention for session updates (b_user_session)

EXPLAIN and EXPLAIN ANALYZE

For each slow query we run EXPLAIN. Critical signs: type = ALL (full scan), rows > 1000, Extra: Using filesort / temporary.

EXPLAIN SELECT be.ID, be.NAME, bp.VALUE 
FROM b_iblock_element be 
LEFT JOIN b_iblock_element_prop_s5 bp ON bp.IBLOCK_ELEMENT_ID = be.ID 
WHERE be.IBLOCK_ID = 12 
AND be.ACTIVE = 'Y' 
AND be.WF_STATUS_ID = 1 
ORDER BY be.SORT ASC 
LIMIT 48 OFFSET 0; 
-- EXPLAIN ANALYZE (MySQL 8.0+) shows actual time 
EXPLAIN ANALYZE SELECT ... ;

EXPLAIN ANALYZE works twice as fast as manual plan analysis — it immediately reveals bottlenecks.

Which indexes are critical for Bitrix?

Several indexes that are often missing in a standard installation:

CREATE INDEX idx_iblock_element_active_sort ON b_iblock_element (IBLOCK_ID, ACTIVE, WF_STATUS_ID, SORT);
CREATE INDEX idx_catalog_price_product_group ON b_catalog_price (PRODUCT_ID, CATALOG_GROUP_ID);
CREATE INDEX idx_user_session_timestamp ON b_user_session (TIMESTAMP_X);

After creating the index, re-run EXPLAIN — the type should change from ALL to ref.

Why is N+1 a common problem in Bitrix ORM?

D7 ORM often generates N+1 queries. Diagnostics via the built-in tracker:

\Bitrix\Main\Application::getConnection()->setTracker(new \Bitrix\Main\DB\SqlTracker(50));

// At the end of the request
$tracker = \Bitrix\Main\Application::getConnection()->getTracker();
foreach ($tracker->getQueries() as $query) {
    if ($query->getTime() > 0.1)
        error_log($query->getSql() . ' [' . $query->getTime() . 's]');
}

// Fix: instead of per-record queries — batch fetch
$ids = array_column($elements->fetchAll(), 'ID');
$prices = PriceTable::getList(['filter' => ['PRODUCT_ID' => $ids]])->fetchAll();
$priceMap = array_column($prices, null, 'PRODUCT_ID');

# Query 1: 12.5k calls, avg 0.8s, 97% of total time
SELECT ... FROM b_iblock_element_prop_s8 ...
# Query 2: 500 calls, avg 2.1s
SELECT ... FROM b_catalog_price ...

Case study: wholesale distributor

From our practice: a Bitrix "Small Business" site, catalog of 28,000 items, 3,000 visitors/day. Server 4 CPU, 8 GB RAM. MySQL load — 85–90% CPU. pt-query-digest showed that 92% of time is spent on b_iblock_element_prop_s8 (string properties table) — full scan on 280,000 rows. A single index idx_prop_s8_element_id reduced load to 15–20% CPU without any code changes. TTFB dropped from 5 s to 1.2 s. On average across our projects, page load time savings are 50–70%. CPU reduction from 85% to 15% results in substantial long-term savings.

Monitoring tools

For more on slow query log: Wikipedia. We also recommend the official D7 ORM documentation for avoiding N+1.

What's included in the work

• Diagnostics: enable slow log, collect data, report with top-20 slow queries.
• Optimization: create indexes, refactor N+1, tune MySQL buffers.
• Monitoring: install PMM or Grafana, set up alerts.
• Documentation and training: describe all changes, recommendations for developers.

If you discover slow queries — order a MySQL profiling audit. We guarantee — after our optimization, database load will drop 3-5 times, and TTFB by 60-80%. Contact us for a free assessment of your project.

Time estimates

Order MySQL query profiling for 1C-Bitrix — get a detailed report and recommendations within a day.