Bitrix SQL Query Performance Audit: Indexes, Caching, D7 ORM

Bitrix SQL Query Performance Audit

On a typical high-load Bitrix site, the CMS generates between 200 and 1000 SQL queries per page. Most of them are repetitive, redundant, or contain SELECT *. Before investing in a more expensive server, it pays to understand which queries are slow and why. Over 10 years of working with Bitrix, we have repeatedly seen projects where a single missing index or absent cache consumed 70% of database resources.

SQL query optimization is not a one-time action but an ongoing process. Our clients achieve up to 50% reduction in server load and 2–5x faster page generation. For example, an e‑commerce store with 50,000 catalog items reduced page load from 12 s to 0.4 s by adding a composite index, saving an estimated $2,400 per month in server costs. Such optimization pays for itself within 2–3 months. Let's look at how to identify and eliminate bottlenecks.

How to Identify Slow SQL Queries

Follow these steps:

1. Enable the Bitrix SQL tracker in the performance panel (/bitrix/admin/perfmon_panel.php) or add programmatic logging:

\Bitrix\Main\Diag\SqlTracker::getInstance()->start(); // ... your code ... $tracker = \Bitrix\Main\Diag\SqlTracker::getInstance(); $tracker->stop(); foreach ($tracker->getQueries() as $query) { echo $query->getSql() . ' — ' . $query->getTime() . 'ms' . PHP_EOL; } 

2. Analyze the tracker: look for queries slower than 50 ms, duplicates (the same query repeated 10–50 times per page), and queries without WHERE on large tables.

3. Additionally, enable slow_query_log in MySQL/MariaDB (long_query_time = 0.5 in my.cnf) — it provides a real picture under production load.

Why Indexes Are Critical for Bitrix

b_iblock_element can contain from 10,000 to 1,000,000 rows. A query like SELECT * FROM b_iblock_element WHERE IBLOCK_ID = 5 AND ACTIVE = 'Y' ORDER BY SORT without an index on (IBLOCK_ID, ACTIVE, SORT) performs a full table scan. Check with EXPLAIN SELECT ... — if you see ALL in the type column, the index is missing.

Bitrix creates indexes during installation but not for user-defined fields (UTS tables like b_uts_iblock_N_single). Indexes on these tables must be added manually. In our experience, proper indexes speed up specific queries by 2–10 times.

— Based on MySQL documentation and best practices from MySQL Index Optimization.

Excessive Field Selection

CIBlockElement::GetList() by default performs a LEFT JOIN with b_iblock_element_iprop and returns dozens of fields, including DETAIL_TEXT that can be megabytes. If the page only needs ID, NAME, and PREVIEW_PICTURE, specify $select explicitly:

CIBlockElement::GetList( ['SORT' => 'ASC'], ['IBLOCK_ID' => 5, 'ACTIVE' => 'Y'], false, ['nPageSize' => 20], ['ID', 'NAME', 'PREVIEW_PICTURE'] ); 

This reduces load by 20–40%.

The N+1 Problem and How to Fix It

The classic scenario: you load 20 elements, then in a loop fetch properties for each with a separate query. That's 21 queries instead of 1–2. In the old API, this is solved with $arSelectFields; in D7, use fetchCollection() with fill(). After optimization, query count drops by 3–5 times.

Repeated Queries for the Same Data

Site settings, user groups, infoblock sections — these are fetched repeatedly on every page. Bitrix's standard cache (BXCache) should handle this, but if caching is disabled or the tagged cache is frequently invalidated, queries hit the database. Bitrix recommends using tagged caching.

What Caching at the Query Level Gives You

If data changes once an hour, there's no need to hit the database on every request. Use \Bitrix\Main\Data\Cache with tagged caching:

$cache = \Bitrix\Main\Data\Cache::createInstance(); $cacheId = 'catalog_top_' . $iblockId; $cachePath = '/catalog/top/'; if ($cache->initCache(3600, $cacheId, $cachePath)) { $data = $cache->getVars(); } elseif ($cache->startDataCache()) { $tagCache = new \Bitrix\Main\Data\TaggedCache(); $tagCache->startTagCache($cachePath); $data = /* your query */; $tagCache->registerTag('iblock_id_' . $iblockId); $tagCache->endTagCache(); $cache->endDataCache($data); } 

When any infoblock element changes, the tag is automatically invalidated. Tagged cache invalidates 3–5 times faster than regular cache.

Optimization Through D7 ORM

D7 ORM allows fine-grained control over queries. Compare:

// Bad: loads all fields and properties $result = \Bitrix\Iblock\ElementTable::getList([ 'filter' => ['IBLOCK_ID' => 5, 'ACTIVE' => 'Y'], ]); // Better: only needed fields, explicit limit, caching $result = \Bitrix\Iblock\ElementTable::getList([ 'select' => ['ID', 'NAME', 'PREVIEW_PICTURE_ID'], 'filter' => ['IBLOCK_ID' => 5, 'ACTIVE' => 'Y'], 'order' => ['SORT' => 'ASC'], 'limit' => 20, 'offset' => 0, 'cache' => ['ttl' => 3600], ]); 

The cache parameter in ORM queries provides built-in caching. D7 ORM is 2–3 times faster than the old API for selections.

Working with Indexes

Adding missing indexes is the quickest fix with the highest impact. Here's a table of the most useful indexes:

An index can be added via SQL or via Bitrix ORM:

$connection = \Bitrix\Main\Application::getConnection(); $connection->queryExecute( "ALTER TABLE b_iblock_element_property ADD INDEX ix_prop_val (IBLOCK_PROPERTY_ID, VALUE(64))" ); 

Be careful with VALUE(64): string fields are indexed by prefix. For numeric values, consider a virtual column. More details in the Wikipedia article on database indexes.

Timeline for Work

What's Included

• Audit of current SQL queries and identification of bottlenecks.
• Design and implementation of indexes, optimization of selections.
• Implementation of caching and migration to D7 ORM.
• Documentation of changes and monitoring recommendations.
• Training for your development team.
• 3-month warranty on optimization correctness.

With 10 years of experience, Bitrix certifications, and over 200 successful projects, we guarantee quality. Every day of high load costs your business — optimization pays off in a few months. Our audit starts at $1,500, and typical annual savings exceed $10,000.

Contact us for an audit — we'll analyze your project and propose concrete steps. Order comprehensive optimization and you'll see the difference in speed.