Optimizing queries to 1C-Bitrix information blocks

Query Optimization for 1C-Bitrix Information Blocks

The Bitrix information block is a flexible but heavy tool. The tables b_iblock_element, b_iblock_element_property, b_iblock_section, and b_iblock_element_property_enum form a relational structure that, when queried carelessly, produces catastrophic execution plans. Running EXPLAIN on a query for a list of 100 products with 5 properties often reveals scans of millions of rows and several seconds of execution time. For catalogs with active traffic, this directly affects TTFB and MySQL load.

Problems with the Old API (CIBlockElement::GetList)

The old Bitrix API (CIBlockElement::GetList) generates a query with a JOIN to b_iblock_element_property for each property when a PROPERTY_ filter is used. With 5 properties in the filter — 5 JOINs. The API also provides no way to explicitly control the query plan.

An additional problem is the arSelect parameter. If "*" is passed or an explicit field list is omitted, the API fetches all fields, including large text descriptions and unnecessary metadata. For a catalog page with 48 products, this multiplies the volume of data transferred from the database by 3–5 times.

Switching to the D7 API (Iblock\ElementTable)

The D7 API (\Bitrix\Iblock\ElementTable) provides a Query Builder with explicit control over select, filter, order, and limit. Every query is translated to SQL and can be inspected via getQuery()->getSql() before execution.

Example of an optimized query for a catalog page:

use Bitrix\Iblock\ElementTable;

$result = ElementTable::getList([
    'select' => [
        'ID',
        'NAME',
        'CODE',
        'PREVIEW_PICTURE',
        'IBLOCK_SECTION_ID',
    ],
    'filter' => [
        '=IBLOCK_ID'        => CATALOG_IBLOCK_ID,
        '=ACTIVE'           => 'Y',
        '=IBLOCK_SECTION_ID' => $sectionId,
    ],
    'order'  => ['SORT' => 'ASC', 'ID' => 'ASC'],
    'limit'  => 24,
    'offset' => $page * 24,
    'cache'  => ['ttl' => 3600],
]);

An explicit select without properties queries only b_iblock_element, without joining b_iblock_element_property. If properties are needed for a subset of products (e.g., only those that made it to the page), they are fetched in a separate query using a list of IDs — the "batch by ID" pattern instead of N+1.

N+1 — the Main Enemy of Information Block Performance

The N+1 problem in the context of Bitrix: you fetch a list of 24 elements, then for each element in a loop you call CIBlockElement::GetProperty() or a separate GetList for related data. Total: 1 list query + 24 property queries + 24 related element queries = 49 queries.

The solution is batch fetching:

// Get IDs of all elements
$ids = array_column($elements, 'ID');

// One query for all properties
$propsResult = \CIBlockElement::GetPropertyValuesArray(
    $ids,
    CATALOG_IBLOCK_ID,
    ['CODE' => ['BRAND', 'COLOR', 'SIZE']]
);

Or via D7 using \Bitrix\Iblock\ElementPropertyTable for fetching properties by a list of IDs.

Component-level vs Data-level Caching

Component cache (file-based, via initCache) is a blunt instrument: the entire HTML fragment is cached and invalidated by the tag IBLOCK_N. For pages with personalization or frequent catalog updates, this is inconvenient.

Data-level caching via ManagedCache with granular tags (by section, by property set) allows only the affected records to be invalidated. The trade-off is the need to explicitly write caching logic at every data access point.

Case Study: Building Materials Catalog, 45,000 SKUs

Before optimization: catalog section page (48 products) — 1.8 s TTFB (without cache), 340 ms with cache. MySQL slow query log showed: one product list query — 640 ms, a "similar products" query — 310 ms, 12 queries total per page.

Problems identified:

• CIBlockElement::GetList with SELECT => "*" and PROPERTY_FILTER => [5 properties]
• N+1 in the "similar products" block: a separate query for each of 6 products
• Section query with full child section tree and no depth limit

What was done:

• Rewrote the main query using D7 with explicit select (8 fields instead of all)
• Batch property query for the "similar products" block
• Section tree depth limited via DEPTH_LEVEL parameter with a section-tagged cache
• Added indexes on b_iblock_element: composite on (IBLOCK_ID, IBLOCK_SECTION_ID, ACTIVE, SORT)

Result: section page (without cache) — 380 ms TTFB, with cache — 45 ms. Database queries: 12 → 4. MySQL load decreased, making it possible to serve 3 times more concurrent users without adding capacity.

Composite Indexes for Information Blocks

Bitrix's standard indexes do not cover all typical query patterns. A critical index for catalogs:

ALTER TABLE b_iblock_element
  ADD INDEX idx_iblock_section_active_sort
    (IBLOCK_ID, IBLOCK_SECTION_ID, ACTIVE, SORT);

Analyzing current indexes via SHOW INDEX FROM b_iblock_element and running EXPLAIN on specific queries from the slow log is a mandatory step before adding any index.

Timeline

For large projects with many custom components — up to 3 weeks.